cycling through worksheets collection w/VBA

Posted by Gail Spurlock on August 29, 2000 2:05 PM

Hello,

I am converting some excel workbooks to a format for importing to Oracle. I am running a VBA macro on the worksheets and need it to cycle through each worksheet and modify the data in the same way. I've tried all of the following code blocks:

For i = 1 To Worksheets.Count
Worksheets(i).Activate

code to run on all worksheets is here

i = i + 1
Next i

*********

For i = 1 To Worksheets.Count

code to run on all worksheets is here

Next i

**********

For each w in Worksheets

code to run on all worksheets is here

Next w

**********

The For i=1 to Worksheets.Count works best. I finds the first worksheet no matter which sheet is active when the macro starts while the others start on the active sheet. But after processing the first worksheet, in all cases it tries to run on the same sheet again.

Do you have any suggestions? What am I doing wrong here?

Thanks much, I'm looking forward to hearing from you.

Best regards,
gail spurlock

Posted by Michael Liu on August 29, 0100 5:27 PM

try:
For each w in Worksheets
w.select
'code to run on all worksheets is here
Next

code to run on all worksheets is here Next i

Posted by Celia on August 29, 0100 6:20 PM

code to run on all worksheets is here Next i

Gail
Your first set of code looks OK. It should loop through each sheet.

The second set of code will not loop unless you either include the line "Worksheets(i).Activate" (like in your first set) or include the sheet identity in range objects in your code like this:-
Worksheets(i).Range("A1")

The third set should work and if you want the macro to loop through the worksheets in the same sequence as the sheet tabs(regardless of the active sheet), then I think this does it.

If you are performing exactly the same action on all the sheets, you might like to try selecting all worksheets and running your code once only. For example :-

Worksheets.Select
ActiveWindow.SelectedSheets.Select
Range("A1").Activate
ActiveCell.Value = "Gail"

This should put "Gail" in cell A1 of all the sheets.

Celia