change activesheet in a for loop

[djamieson17]

Hi All,

I have an array that contains a list of worksheet names. I want to change the activesheet in a loop.

psuedo-code:

array = ("sheet1" "sheet2" "sheet3")

for i = 1 to length of array
change active sheet to array(i)
... rest of macro ...
next j

I've tried a number of (what I thought were) obvious ways, but cannot get it to work.

Thanks,

Dave

 

[Norie]

Dave

Why do you want to change the active sheet?

Does the rest of the macro rely on the sheet being active?

Why not reference the sheet instead of activing it?

arrShts = Array("Sheet1", "Sheet2", "Sheet3")
 
For I = LBound(arrShts) To UBound(arrShts)
 
      Set ws = Worksheets(arrShts(i))
       ' rest of code
Next I

Even if all you want to do is activate the sheet you can still use this code and simply add ws.Activate.

 

[djamieson17]

Norie,

I am trying to (for each sheet in my array) unhide all cells, then hide all rows where a 0 exists in column a. my code so far is:


'asSht is my array
lngth = Application.CountA(asSht)

For j = 1 To lngth
Set wks = Worksheets(asSht(j)) ''i want to change the activesheet here!
ActiveSheet.Rows.EntireRow.Hidden = False
For Each c In ActiveSheet.Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
If c = "0" Then
ActiveSheet.Rows(c.Row).Hidden = True
End If
Next c
Next j

I am just struggling to change the activesheet. If you can help me, or think of a more efficient way of doing this, I would be very grateful.

Thanks,

Dave

 

[Norie]

Dave

Change Activesheet to wks, then you don't need to activate the sheet.

 

[rosenbe]

If you are really keen on activating, do a wks.Activate call before calling ActiveSheet.

 

[djamieson17]

Guys,

Thanks very much. Works fine now. You were right enough, there was no need to activate each sheet when I could just reference them.

Thanks again,

Dave