Having trouble looping macro through active worksheets

[mccabe2017]

This should be simple but I am having trouble. I am attempting to run a macro to hide columns through multiple worksheets of a workbook. but I do not want it to run through specific worksheet names. I tried the following but it is grouping this column 8 time on the first worksheet it finds past the excluded worksheets and then giving me a runtime error. Any idea on what I am getting wrong and how to fix?

Sub Columns_Group()
    Dim ws As Worksheet
    On Error Resume Next
    For Each ws In ActiveWorkbook.Worksheets
    If Not ws.Name = "Individual Summary" 
    And Not ws.Name = "Leadership Summary" 
    And Not ws.Name = "Pivotsum" 
    And Not ws.Name = "LeadershipPivot" 
    And Not ws.Name = "Starspivot" A
    And Not ws.Name = "Stars Summary" A
    And Not ws.Name = "Full Data Sheet" Then Columns("C : D").Columns.Group
Next ws
End Sub

[Paul_Hossler]

I added [CODE] ... [/CODE] tags around your macro. You can do that using the [#] icon

Where to start?

1. Resume Next hides errors and probably shouldn't be used

2. You have to use the line continuation character (_ or underscore )if you divide one logical line onto several physical lines

If Not ws.Name = "Individual Summary" _

3. I don't know what the "A" is for on the last two sheet name lines

4. The .Columns is redundant.

 Columns("C:D").Group

is sufficient

5. Just

Columns("C:D")

without a worksheet will always use the ActiveSheet. Probably use

ws.Columns("C:D").Group

I'd simplify (in my opinion) and use Select Case. Not tested but this is the idea

Option Explicit

Sub Columns_Group()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        Select Case ws.Name
            Case "Individual Summary", "Leadership Summary", "Pivotsum", "LeadershipPivot", "Starspivot", "Stars Summary", "Full Data Sheet"
                     'do nothing
            Case Else
                ws.Columns("C:D").Group
        End Select
    Next ws
End Sub

Or your original

 
Sub Columns_Group1()
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        If Not ws.Name = "Individual Summary" _
            And Not ws.Name = "Leadership Summary" _
            And Not ws.Name = "Pivotsum" _
            And Not ws.Name = "LeadershipPivot" _
            And Not ws.Name = "Starspivot" _
            And Not ws.Name = "Stars Summary" _
            And Not ws.Name = "Full Data Sheet" Then
                ws.Columns("C:D").Group
        End If
    Next ws
End Sub

[mccabe2017]

Thanks for the response Paul. I've tried it both ways (with your modifications to my original and your suggested new way) both are giving me the same result. It's repeating the grouping 8 times on the same worksheet then giving me a runtime error.

[SamT]

It's repeating the grouping 8 times on the same worksheet then giving me a runtime error.

Was it the Active Sheet? Are there 8 sheets not Excluded? Is there a merged cells in Columns C or D on any sheet?

Try viewing a different sheet before running the macro.

I like InStr() to find the excluded sheets.

Sub Columns_Group() 
    Dim ws As Worksheet 

Const Excluded As String = "Individual Summary Leadership Summary Pivotsum LeadershipPivot Starspivot Stars Summary Full Data Sheet" 
 
   For Each ws In ActiveWorkbook.Worksheets 
        If InStr(Excluded,  ws.Name) = 0 then ws.Columns("C:D").Group 
    Next ws 
End Sub

[Paul_Hossler]

Thanks for the response Paul. I've tried it both ways (with your modifications to my original and your suggested new way) both are giving me the same result. It's repeating the grouping 8 times on the same worksheet then giving me a runtime error.

Doesn't do that for me

Are you sure you have the "ws." in this line?

ws.Columns("C:D").Group

is the macro in the active workbook?

[mdmackillop]

Post a blank copy of the workbook so we can test on your setup. We can't know of merged cells, spelling errors etc. which might affect the outcome. Paul's solution works for me.

[mikerickson]

Perhaps

Dim oneSheet as Worksheet

For Each oneSheet in ActiveWindow.SelectedSheets
    ' ....
Next oneSheet

[mccabe2017]

I'm going to upload a file here. I even ran into the same sort of issue when using the Grouping file Paul was kind enough to provide. I found another code variant I decided to use as I thought that calling out the specific sheets would work better than trying to use if/then syntax or select case.

But again, I continue to run into problem where the code seems to run continuously on the first worksheet in the array until I get an error message. I can not get any of the suggested methods to work through each worksheet.

[Paul_Hossler]

Simplifying to just the loop part ...

The Array() has "Curly", but the workbook has a sheet named "Cury"

Option Explicit
Sub Test2()
  Dim InxW As Long
  Dim WshtNames As Variant
  WshtNames = Array("Moe", "Larry", "Curly", "Spongebob", "Squarepants")
  
  For InxW = LBound(WshtNames) To UBound(WshtNames)
    With Worksheets(WshtNames(InxW))
            
        MsgBox InxW & " -- " & .Name
    
    End With
  Next InxW
End Sub

Edit -- part 2

You need the 'dot' when you're working with a 'With' otherwise it most likely will default to the ActiveSheet

BTW, not need to select something to work on it (usually)

Option Explicit
Sub Test2()
  Dim InxW As Long
  Dim WshtNames As Variant
  WshtNames = Array("Moe", "Larry", "Curly", "Spongebob", "Squarepants")
  
  For InxW = LBound(WshtNames) To UBound(WshtNames)
    With Worksheets(WshtNames(InxW))
        .Columns("C:D").Group
        .Columns("G:H").Group
        .Columns("J:J").Group
        .Columns("L:N").Group
        .Columns("P:W").Group
        .Columns("Z:AC").Group
        .Columns("AF:AG").Group
        .Columns("AL:AL").Group
        .Columns("AN:AN").Group
        .Columns("AP:AP").Group
        .Columns("AR:AR").Group
        .Columns("AV:AV").Group
        .Columns("BA:BC").Group
        .Columns("BE:BE").Group
        .Columns("BH:BK").Group
        .Columns("BR:BT").Group
        .Columns("BW:BZ").Group
        .Columns("B:B").ColumnWidth = 19.29
        .Columns("G:H").ColumnWidth = 0
        .Columns("F:F").ColumnWidth = 22.57
        .Select
        .Range("AD2").Select
        ActiveWindow.FreezePanes = True
    End With
  Next InxW
End Sub