Exclude some sheets

**maxwell13** · 05-01-2007, 10:06 AM

Hi All

I would like to run a macro on all the sheets in a workbook except for two sheets named DataSheet and ListSheet. The remaining sheets are named Sheet1 Sheet2 etc. The number of sheets may vary from 2 through to 20.

I have searched these forums and found reference to this subject including various examples of code which handles this task.

Unfortunately i can't seem to adapt any of them to do exactly what i want

Any assistance would again be very appreciated.

Thanks

jeff

**sweep** · 05-01-2007, 10:17 AM

Sub loopsheets()
Dim sheetnumber As Variant
For sheetnumber = 1 To 20
Worksheets("Sheet" & sheetnumber).Select
Your code here
Next
End Sub

**maxwell13** · 05-01-2007, 05:21 PM

Hi Dave

Thanks for your reply.

Your code works great if there are exactly 20 sheets in the workbook. The number may vary however from 2 up to 20 so i need it to run without specifying exact numbers of sheets. Can you help further??

Thanks again

Jeff

**Steel Monkey** · 05-01-2007, 08:41 PM

Add something like the following:

Dim wscount As Integer

wscount = Application.Worksheets.Count

Then change:

For sheetnumber = 1 To 20

to

For sheetnumber = 1 To wscount

That should do what you need

Let me know how it goes!

**maxwell13** · 05-02-2007, 12:03 AM

thanks Steel Monkey

Your suggestions did do as i wanted however the code runs correctly and then i get a subscript out of range error on the highlighed line

Dim wscount As Integer

wscount = Application.Worksheets.Count
Dim sheetnumber As Variant
For sheetnumber = 1 To wscount
Worksheets("Sheet" & sheetnumber).Select

Range("a1").ClearContents

Next
End Sub

What do you think?

Thanks

Jeff

**Steel Monkey** · 05-02-2007, 09:18 PM

Thta makes sense as it would be trying to select a sheet called "sheetDataList" which isnt there.

What you would be better off doing is looping through ALL sheets and not setting a number to loop through. Please see the below code which will loop through all sheets and check if the currentsheet is named Datasheet or Listsheet and if if it will go to the next sheet.

Enter your code on what you want done and it should work fine

Sub test()
Dim wsSheet As Worksheet

    For Each wsSheet In Worksheets
        If wsSheet.Name <> "DataSheet" Then
              If wsSheet.Name <> "ListSheet" Then
              'your code here
              End If
    End If
    
    Next wsSheet
End Sub

Let me know how you go!