I run one macro to group 16 non-contiguous worksheets in the same workbook. The
ID: 3560545 • Letter: I
Question
I run one macro to group 16 non-contiguous worksheets in the same workbook. The macro activates the first WS and initiates a For Each Loop to cycle through the WSs and call a second Macro the runs an Advanced Filter on each WS. It works fine for the first WS and appears cycles through the loop 16 times and completes. The 1st macro is not activating WS 2 through WS16 and the 2nd macro runs the advanced filter on the active WS. The filter operates 16 times on the Active WS(the 1st) The sheets are identical except for the data and filter criteria. I don't know how to instruct it to activate the next WS before executing the Next WS statement. As you might guess I am new to VBA coding, but I have recorded and edited several macros over the past year. Below is the code for both macros. I would greatly appreciate any help. Sub SelWSGroup()[CODE]
' SelWSGroup Macro
' Selects all 16 non-contiguous Lookup Tables in this WB
Sheets(Array("Venue 1 Lookup Table Day 1", "Venue 1 Lookup Table Day 2", _
"Venue 1 Lookup Table Day 3", "Venue 1 Lookup Table Day 4", _
"Venue 2 Lookup Table Day 1", "Venue 2 Lookup Table Day 2", _
"Venue 2 Lookup Table Day 3", "Venue 2 Lookup Table Day 4", _
"Venue 3 Lookup Table Day 1", "Venue 3 Lookup Table Day 2", _
"Venue 3 Lookup Table Day 3", "Venue 4 Lookup Table Day 1", _
"Venue 4 Lookup Table Day 2", "Venue 4 Lookup Table Day 3", _
"Venue 4 Lookup Table Day 4")).Select
Sheets("Venue 1 Lookup Table Day 1").Activate
For Each ws In Windows(1).SelectedSheets
Call ExtractFieldIDs
Next ws
Sheets("Venue 1 Lookup Table Day 1").Select
End Sub
Sub ExtractFieldIDs()
'Extracts Field IDs for all Venue Lookup Tables
ActiveSheet.Range("N2").Select
Sheets("Modify DB").Columns("H:I").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=ActiveSheet.Range("Criteria"), CopyToRange _
:=ActiveSheet.Range("Extract"), Unique:=True
Columns("N:O").EntireColumn.AutoFit
End Sub
[/CODE] Thank you.
Explanation / Answer
Really a personal preference. I know some folks that cannot work without almost everything being referred to as a defined name but others (like myself) are comfortable with A1 style cell references. Since you would have to reduce the scope of the defined name to worksheet (can't have them all called the same thing in a workbook scope), using A1 style cell references make more sense; just so long as they are all in the same place.
Addendum: you could use Range("A1") or Range("$A$1"). Since the actual references are text, they aren't going to change so the $ absolute markers are unnecessary.
A sub can have a parameter passed into it. In the following, I'm passing over the worksheet object.
Sub SelWSGroup()
Dim w As Long, vWSs As Variant
vWSs = Array("Venue 1 Lookup Table Day 1", "Venue 1 Lookup Table Day 2", _
"Venue 1 Lookup Table Day 3", "Venue 1 Lookup Table Day 4", _
"Venue 2 Lookup Table Day 1", "Venue 2 Lookup Table Day 2", _
"Venue 2 Lookup Table Day 3", "Venue 2 Lookup Table Day 4", _
"Venue 3 Lookup Table Day 1", "Venue 3 Lookup Table Day 2", _
"Venue 3 Lookup Table Day 3", "Venue 4 Lookup Table Day 1", _
"Venue 4 Lookup Table Day 2", "Venue 4 Lookup Table Day 3", _
"Venue 4 Lookup Table Day 4")
For w = LBound(vWSs) To UBound(vWSs)
Call ExtractFieldIDs(Sheets(vWSs(w)))
Next w
Sheets("Venue 1 Lookup Table Day 1").Select
End Sub
Sub ExtractFieldIDs(ws As Worksheet)
With ws
Sheets("Modify DB").Columns("H:I").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("Criteria"), _
CopyToRange:=.Range("Extract"), Unique:=True
.Columns("N:O").EntireColumn.AutoFit
End With
End Sub
I cannot really test this but the syntax will compile. If the code is incorrect, perhaps you can use the technique and modify to suit your purposes.
I just modified the code by chopping down the worksheets to fours and set up a full test environment with a workbook containing all four worksheets. The helper routine simply dumps the worksheet name into the VBE's Immediate window.
Option Explicit
Sub SelWSGroup()
Dim w As Long, vWSs As Variant
vWSs = Array("Venue 1 Lookup Table Day 1", "Venue 1 Lookup Table Day 2", _
"Venue 1 Lookup Table Day 3", "Venue 1 Lookup Table Day 4")
For w = LBound(vWSs) To UBound(vWSs)
Call ExtractFieldIDs(Sheets(vWSs(w)))
Next w
Sheets("Venue 1 Lookup Table Day 1").Select
End Sub
Sub ExtractFieldIDs(ws As Worksheet)
With ws
Debug.Print .Name
.Columns("N:O").EntireColumn.AutoFit
End With
End Sub
That ran through exactly as expected and listed the four worksheet names in the Immediate window (e.g. Ctrl+G). Perhaps the copy and paste from my reply in a web browser brought in non-breaking spaces instead of conventional spaces.