Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: Activate sheets within a loop
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: Activate sheets within a loop
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. "CM4@FL" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: Activate sheets within a loop
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 -- Cheers, Ryan "CM4@FL" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: Activate sheets within a loop
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 -- Cheers, Ryan "CM4@FL" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop to Filter, Name Sheets. If Blank, Exit Loop | Excel Programming | |||
visual basic - select/activate but don't show sheets | Excel Programming | |||
Activate Sheet Best Practice (Y/N?) when xFer data between sheets? | Excel Programming | |||
Activate all sheets | New Users to Excel | |||
Any difference between Sheets Activate and Select? | Excel Programming |