Running Macro/VBA in multiple worksheets.

[Guest]

I am trying to run a Macro (which works perfectly) in “All†Worksheets of a
particular Workbook. I currently have seven Worksheets in the Workbook and
have to select each Worksheet and manually run the Macro. I will eventually
have 50 (maybe more) Worksheets where the Macro will need to be run. I have
used the “Private Sub†code in VBA but can only get it to execute the Macro
in the active Worksheet when the Workbook opens.

Is there code that could/would run the Macro in “All†the Sheets? Sheet1,
Sheet2, Sheet3, Sheet4…

Any help would be greatly appreciated.

 

[Bob Phillips]

For Each sh In ActiveWorkbook.Worksheets
Call MyMacro
Next sh

You might need to pass the sh object to the macro so that it can work on
that, like this

For Each sh In ActiveWorkbook.Worksheets
Call MyMacro(sh)
Next sh

Sub MyMacro(pSheet as worksheet)
With pSheet
'...
End With
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Public Sub ProcessAllWorksheets()
Dim wk As Worksheet
For Each wk In Application.Worksheets
'call your macro
Next wk
End Sub


HTH,

 

[Guest]

Gary,

I've tried the new code and it works except that instead of running the
Macro in each sheet, it runs the Macro 7 times which is the number of sheets
I currently have in the workbook.

The following is the code with your suggested addition.

Public Sub ProcessAllWorksheets()
Dim wk As Worksheet
For Each wk In Application.Worksheets

' Update_Research_Data Macro
'
Range("A10").Select
Selection.EntireRow.Insert
Range("A9:F9").Select
Selection.Copy
Range("A10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("G9:U9").Select
Selection.Copy
Range("G10").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A9").Select
Next wk

 

[Guest]

Bob,

I'm not sure if you reviewed the reply I sent to Gary but basically I had
the same result with your suggestion. The Marco ran 7 times which I assume
was 1 for each Worksheet in the Workbook. It doesn't seem to be switching to
the other Worksheets.

Any other thoughts/suggestions are welcome.

Thanks.

 

[Bob Phillips]

It will run 7 time but you need to adjust your code for each individual run
to address the correct sheet. I showed the basic mechanism earlier, but
without seeing your macro, I cannot absolutely state what would need
adjusting.

There is no need to activate each sheet, you can work on them without doing
so.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Bob,

Thank you for your response. While I know my way around Excel, I've just
started trying my "Luck" With Macros.

The following is the code for the Macro that I run. At this point, I can
make it run after I open the Workbook using the ALT F8 command but only in
the active Worksheet. I have to switch manually from Sheet to Sheet to
execute the Macro in all Sheets. I would like to call the Macro once and
have it run once in all Worksheets in the Workbook. The way I would like to
run the Macro is manually, one time, after the Workbook is opened and have it
run the Macro in each Worksheet in the Workbook.

' Update_Research_Data Macro
'
Range("A10").Select
Selection.EntireRow.Insert
Range("A9:F9").Select
Selection.Copy
Range("A10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("G9:U9").Select
Selection.Copy
Range("G10").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A9").Select
End Sub

Thanking you in advance for any help you can provide. It is greatly
appreciated.

 

[Tom Ogilvy]

Sub UpdateResearch()
' Update_Research_Data Macro
'
Dim sh as Worksheet
for each sh in Workbook.Worksheets
sh.Select
Range("A10").Select
Selection.EntireRow.Insert
Range("A9:F9").Select
Selection.Copy
Range("A10").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False
Range("G9:U9").Select
Selection.Copy
Range("G10").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Application.CutCopyMode = False
Range("A9").Select
Next Sh
end sub

or

Sub UpdateResearch1()
for each sh in Workbook.Worksheets
sh.Select
sh.Range("A9:F9").copy
sh.Range("A10").PasteSpecial Paste:=xlPasteValues
sh.Range("G9:U9").copy
sh.Range("G10").PasteSpecial Paste:=xlPasteFormulas
sh.Range("A9").Select
Next
End Sub

 

[Guest]

Tom,

Tried the code as suggested and got a 'runtime error "424". Reviewed some
samples and some tests that I had previously tried and had been suggested by
Gary and Bob and was able to get the Macro to run perfectly. The only change
I made to your suggested code was to change For Each sh In
Workbook.Worksheets - to - For Each sh In ActiveWorkbook.Worksheets.

I have run the Macro in my original and added new sheets and it still runs
properly.

Thank you very much for your help. I do appreciate it. And those I am
working with appreciate it as well. I will post again if needed. Thanks
again.

 

[Tom Ogilvy]

Yes it should be Activeworkbook or ThisWorkbook depending.

Sometimes I make typos. Glad you got the point.