Deleting Sheets

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

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" <oakman.2cl163_1155646808.0373@excelforum-nospam.com> wrote in
message news:oakman.2cl163_1155646808.0373@excelforum-nospam.com...
>
> 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

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" <oakman.2cl3y3_1155650410.0151@excelforum-nospam.com> wrote in
message news:oakman.2cl3y3_1155650410.0151@excelforum-nospam.com...
>
> 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
>