Deleting Sheets

[oakman]

Greetings,

I am not at all experienced in VBA, but I thought that I’d give this
shot. However, I cannot get it to work. I have a file in which th
first four sheets are permanent. Other sheets are created every mornin
depending on a set of conditions. The number of sheets being created i
different every day. I am trying to come up with a code that woul
delete every sheet after sheet 4Can some one please take a look at m
code and help me. I would appreciate it very much! Currently, I get
Compile error for the word “Worksheets” in the line “Set Sh
Worksheets(Array(1), (2), (3), (4))” Not quite sure what that reall
means. Thank you

Sub deletesheet()

Dim x As Integer
Dim Sh As Worksheet
Dim Daysheet As Worksheet
Dim WrkBook As Workbook

x = 1
Set Sh = Worksheets(Array(1), (2), (3), (4))
Set WrkBook = Currentfile

For Each Daysheet In WrkBook
Worksheets.Delete After:=Sh

Next x

x = x + 1

End Su

 

[Norman Jones]

Hi Oakman,

Try something like:

'=============>>
Public Sub DeleteSheets()
Dim WB As Workbook
Dim SH As Worksheet
Dim arr As Variant

Set WB = Workbooks("Import.xls") '<<==== CHANGE

arr = Array("Sheet1", "Sheet2", _
"Sheet3", "Sheet4") '<<==== CHANGE

Application.DisplayAlerts = False
For Each SH In WB.Worksheets
If IsError(Application.Match(SH.Name, arr, 0)) Then
SH.Delete
End If
Next SH
Application.DisplayAlerts = True

End Sub
'<<=============

 

[Dave Peterson]

Keep the 4 leftmost sheets? And delete everything else?

dim iCtr as long
application.displayalerts = false
for ictr = sheets.count to 5 step -1
sheets(ictr).delete
next ictr
application.displayalerts = true

It just starts from the rightmost and deletes all the sheets (through sheet 5).

 

[oakman]

Thank you so much for your help!
Your responses are very instructive.
They both work, albeit in different ways.
I hope to learn something form each.
Thank you

 

[Norman Jones]

Hi Oakman,

Dave's code is simpler but assumes that the sheets to be retained are the
first four sheets in the workbook.

My code deletes all worksheets except for four named worksheets,
irrespective of the position of any of the sheets.

If you are sure that the sheets to be retained are the first four sheets,
then go with Dave's less verbose code. If, conversely, the location may
vary, try my suggested code.