VBA: Activate sheets within a loop

[CM4@FL]

I want to loop through a list of sheets, why won't the following code allow
me to activate a sheet? Thanks for your help in advance!

Sub LoopThroughSheets()

Dim wSheet As Worksheet

i = 5
wSheet = Sheets("Print Tracks").Range("G" & i).Value

Do While wSheet <> ""

Sheets(wSheet).Activate
Calculate

i = i + 1
wSheet = Sheets("Print Tracks").Range("G" & i).Value

Loop

End Sub

 

[Mike H]

Hi,

There's no need to activate the sheet to calculate, try this

Sub LoopThroughSheets()
Dim wSheet As String
i = 5
wSheet = Sheets("Print Tracks").Range("G" & i).Value
Do While wSheet <> ""
Sheets(wSheet).Calculate
i = i + 1
wSheet = Sheets("Print Tracks").Range("G" & i).Value
Loop
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Ryan H]

You have wSheet declared as a worksheet. I assume it needs to be declared as
a String, because it looks like you are looping through a list of sheet
names. This is how you should declare it

Dim wSheet As String

I actually recommend you looping through your sheets like this instead.
You don't have to activate the sheet to calculate it. Hope this helps! If
so, let me know, click "YES" below.

Sub LoopThroughSheets()

Dim i As Long

i = 5
Do While Sheets("Print Tracks").Cells(i, "G").Value <> ""
Sheets(Cells(i, "G").Value).Calculate
i = i + 1
Loop

End Sub

 

[Ryan H]

It's actually better to loop through objects (in your case the object is
cells) with the For Each...Loop instead of For...Loop. Try this code. If
your list of sheet names in Col. G is continues without any blanks in between
sheet names this code would work a little better. Hope this helps! If so,
let me know, click "YES" below.

Sub LoopThroughSheets()

Dim LastRow As Long
Dim MyRange As Range
Dim cell As Range

With Sheets("Print Tracks")
' find last row in column G
LastRow = .Cells(Rows.Count, "G").End(xlUp).Row
' set range to scan with loop
Set MyRange = .Range("G5:G" & LastRow)
End With

' scan each cell in range of sheet names
For Each cell In MyRange
Sheets(cell.Value).Calculate
Next cell

End Sub