Clear workbook

[Lucile]

Hi all,

I need to add a piece of code at the beginning of my program that checks if
the workbook is empty or not (I just want 3 blank sheets). And if it is not
empty (charts, sheets with data...) it needs to clear/delete everything and
keep only 3 blank sheets....

Thanks a lot!

 

[Dave Peterson]

Why not just start a new workbook with 3 worksheets?

Option Explicit
Sub testme()
Dim NewWkbk As Workbook
Dim CurSheetsInWorkbook As Long

CurSheetsInWorkbook = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 3
Set NewWkbk = Workbooks.Add
Application.SheetsInNewWorkbook = CurSheetsInWorkbook

'then work with newwkbk

End Sub

 

[Gary''s Student]

Better to start with fresh, clean, sheets rather than try to launder the old
ones:

Sub cleanup()
Worksheets.Add before:=Worksheets(1)
Worksheets.Add before:=Worksheets(1)
Worksheets.Add before:=Worksheets(1)
n = Worksheets.Count
For i = n To 4 Step -1
Application.DisplayAlerts = False
Sheets(i).Delete
Next
End Sub

 

[Lucile]

Excellent!
Thank you very much!

Better to start with fresh, clean, sheets rather than try to launder the old
ones:

Sub cleanup()
Worksheets.Add before:=Worksheets(1)
Worksheets.Add before:=Worksheets(1)
Worksheets.Add before:=Worksheets(1)
n = Worksheets.Count
For i = n To 4 Step -1
Application.DisplayAlerts = False
Sheets(i).Delete
Next
End Sub

 

[Rick Rothstein]

For future reference, these 3 lines...

Worksheets.Add before:=Worksheets(1)
Worksheets.Add before:=Worksheets(1)
Worksheets.Add before:=Worksheets(1)

can be replaced by this single line...

Worksheets.Add Before:=Worksheets(1), Count:=3