Exclude some sheets

**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!

**maxwell13** · 05-02-2007, 11:44 PM

Using that piece of code the action is performed only on the sheet called DataSheet and not on any of the others.

I think i understand what you are trying to do but it seems to be working in reverse??????

Jeff

**Steel Monkey** · 05-02-2007, 11:53 PM

Ok give this one a go!

Sub test()
Dim wsSheet As Worksheet

    For Each wsSheet In Worksheets
    Select Case wsSheet.Name
    Case "ListSheet"
    
    Case "DataSheet"

    Case Else
    wssheet.select
    'Your Code Here
    End Select
    Next wsSheet
End Sub

**Steel Monkey** · 05-03-2007, 12:36 AM

You can use either of the two examples above but if you use the first one just add wssheet.select and it will work perfectly:

Sub test()
Dim wsSheet As Worksheet

    For Each wsSheet In Worksheets
        If wsSheet.Name <> "DataSheet" Then
              If wsSheet.Name <> "ListSheet" Then
              wsSheet.Select ' we need to add this line
              'yourcodehere
              End If
    End If
    
    Next wsSheet
End Sub