VBA applies to selected worksheets

[Apple08]

Hi All

I have a macro which is applied to all worksheets except 'configuration' or 'project managers' worksheets. However, when I run it, it seems the macro also affect these two worksheets. Please could someone help me to resolve this? Many thanks.

The macro starts with:

Sub report()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In Worksheets
If sh.Name <> "configuration" Or sh.Name <> "project managers" Then
    sh.Activate

 

[Greg Truby]

Oh, and one more thought... I'd stick with the FOR EACH WORKSHEET construct you started with, i.e. I would not opt for the approach Teeks2k used (for i = 1 to .count) unless I had a specific reason to expand my testing. Teeks2k used the Sheets collection, not the Worksheets collection which would open expand the scope of your loop. To me the approach you're already using is more robust.

Sub ForEachComparison()

Rem     create a workbook with a couple of worksheets
Rem     and a couple of chart sheets


    Dim objSheet    As Object, _
        wksItem     As Excel.Worksheet
    
    For Each objSheet In Sheets: Debug.Print objSheet.Name, TypeName(objSheet): Next objSheet
    Debug.Print String(20, Chr(151))
    For Each wksItem In Worksheets: Debug.Print wksItem.Name, wksItem.CodeName: Next wksItem


End Sub

 

[Teeks2k]

Even easier

If Ucase(sh.Name) <> "CONFIGURATION" Or sh.Name <> "PROJECT MANAGERS" Then

Yes, I was going by a code I currently use so I stuck with that format, but this is much easier for his/her purpose.

 

[Teeks2k]

Oh, and one more thought... I'd stick with the FOR EACH WORKSHEET construct you started with, i.e. I would not opt for the approach Teeks2k used (for i = 1 to .count) unless I had a specific reason to expand my testing. Teeks2k used the Sheets collection, not the Worksheets collection which would open expand the scope of your loop. To me the approach you're already using is more robust.

Could you explain the difference between sheets and worksheets collection? I thought both approaches were essentially the same thing, I am just used to using For v For Each

 

[Greg Truby]

Could you explain the difference between sheets and worksheets collection? I thought both approaches were essentially the same thing, I am just used to using For v For Each

Teeks,

The Sheets collection includes all of the sheets in the workbook. In modern Excel, that means worksheets and chart sheets. In ancient Excel, that also meant dialog sheets and macro sheets. One can still create dialog sheets and macro sheets, though I can't think of a good reason to do so. Run the following and note the output to the debug window. Note that a macro sheet will return a typename of "Worksheet" but a type enumeration (3 or 4) different than a normal worksheet (-4167).

Sub SheetsVersusWorksheets()

    Dim objSheet    As Object, _
        wksItem     As Excel.Worksheet, _
        s$
        
    Workbooks.Add XlWBATemplate.xlWBATWorksheet
        
    With Sheets
        .Add Type:=XlSheetType.xlWorksheet
        .Add Type:=XlSheetType.xlExcel4MacroSheet
        .Add Type:=XlSheetType.xlChart
        .Add Type:=XlSheetType.xlDialogSheet
        .Add Type:=XlSheetType.xlExcel4IntlMacroSheet
    End With
    
    On Error Resume Next
    For Each objSheet In Sheets
        Let s = objSheet.Name
        Let s = s & vbTab & TypeName(objSheet)
        Let s = s & vbTab & objSheet.Type
        If Err.Number <> 0 Then
            Let s = s & vbTab & "(no type property)"
            Err.Clear
        End If
        Debug.Print s
    Next objSheet
    
    Debug.Print String(20, Chr(151))
    
    For Each wksItem In Worksheets: Debug.Print wksItem.Name, wksItem.CodeName: Next wksItem


End Sub