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

--
oakma
-----------------------------------------------------------------------
oakman's Profile: http://www.excelforum.com/member.php...nfo&userid=917
View this thread: http://www.excelforum.com/showthread.php?threadid=57175

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
'<<=============


--
---
Regards,
Norman



"oakman" wrote in
message ...

Greetings,

I am not at all experienced in VBA, but I thought that I'd give this a
shot. However, I cannot get it to work. I have a file in which the
first four sheets are permanent. Other sheets are created every morning
depending on a set of conditions. The number of sheets being created is
different every day. I am trying to come up with a code that would
delete every sheet after sheet 4Can some one please take a look at my
code and help me. I would appreciate it very much! Currently, I get a
Compile error for the word "Worksheets" in the line "Set Sh =
Worksheets(Array(1), (2), (3), (4))" Not quite sure what that really
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 Sub


--
oakman
------------------------------------------------------------------------
oakman's Profile:
http://www.excelforum.com/member.php...fo&userid=9172
View this thread: http://www.excelforum.com/showthread...hreadid=571754

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 wrote:

Greetings,

I am not at all experienced in VBA, but I thought that I’d give this a
shot. However, I cannot get it to work. I have a file in which the
first four sheets are permanent. Other sheets are created every morning
depending on a set of conditions. The number of sheets being created is
different every day. I am trying to come up with a code that would
delete every sheet after sheet 4Can some one please take a look at my
code and help me. I would appreciate it very much! Currently, I get a
Compile error for the word “Worksheets” in the line “Set Sh =
Worksheets(Array(1), (2), (3), (4))” Not quite sure what that really
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 Sub

--
oakman
------------------------------------------------------------------------
oakman's Profile: http://www.excelforum.com/member.php...fo&userid=9172
View this thread: http://www.excelforum.com/showthread...hreadid=571754

--

Dave Peterson

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


--
oakman
------------------------------------------------------------------------
oakman's Profile: http://www.excelforum.com/member.php...fo&userid=9172
View this thread: http://www.excelforum.com/showthread...hreadid=571754

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.


---
Regards,
Norman



"oakman" wrote in
message ...

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


--
oakman
------------------------------------------------------------------------
oakman's Profile:
http://www.excelforum.com/member.php...fo&userid=9172
View this thread: http://www.excelforum.com/showthread...hreadid=571754