MrExcel Publishing
Your One Stop for Excel Tips & Solutions

calling a macro to run on several sheets at once


Posted by rm on December 24, 2001 10:47 AM

I am using macros to update several worksheets in a workbook. I simply record the keystrokes of the changes I need to make to the worksheets and then execute the macro on each worksheet. This works fine, but the workbooks have between 40-50 sheets each. I would like to know if there is a way to run the macro just once to update all of the worksheets instead of run the macro on each worksheet (thus having to run the macro 40-50 times) which is what I am currently doing. (Not that I am necessarily lazy, just a time saver.) Any help is much appreciated.

rm

p.s. all of the worksheets in the workbooks are identical in regards to design and formulas--just the information the user inputs on the sheets is different (they are employee timecard records).


Posted by Robb on December 24, 2001 11:47 AM

rm

Try using something like this to loop through the sheets. Basically, ittakes each sheet in the workbook and does something
with that sheet before moving on to the next. In this case, inserts "Fill" in range A1. You will need to insert your code.
If you need to, you may also replace ThisWorkbook with another workbook identifier.

Sub allSheets()
For Each sh In ThisWorkbook.Worksheets
With sh
.[A1] = "Fill" 'Replace this with your code
End With
Next sh
End Sub

Any help?

Regards

Robb

Posted by rm on December 24, 2001 1:49 PM

Robb,
thanks for the tip...I will try using it when I get back to my desk on Wednesday...happy holidays to you and to all...rm

: I am using macros to update several worksheets in a workbook. I simply record the keystrokes of the changes I need to make to the worksheets and then execute the macro on each worksheet. This works fine, but the workbooks have between 40-50 sheets each. I would like to know if there is a way to run the macro just once to update all of the worksheets instead of run the macro on each worksheet (thus having to run the macro 40-50 times) which is what I am currently doing. (Not that I am necessarily lazy, just a time saver.) Any help is much appreciated.