PDA

View Full Version : Having trouble looping macro through active worksheets



mccabe2017
05-18-2017, 12:46 PM
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
05-18-2017, 01:06 PM
I added
... 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
05-18-2017, 02:16 PM
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
05-18-2017, 03:14 PM
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
05-18-2017, 04:37 PM
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
05-19-2017, 02:25 AM
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
05-19-2017, 06:55 AM
Perhaps


Dim oneSheet as Worksheet

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

mccabe2017
06-07-2017, 01:10 PM
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
06-07-2017, 05:35 PM
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