Running Macro/VBA in multiple worksheets.
 

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.

--
SHD

Running Macro/VBA in multiple worksheets.
 

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)

"YellowBird" wrote in message
...
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.

--
SHD

Running Macro/VBA in multiple worksheets.
 

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


HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"YellowBird" wrote:

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.

--
SHD

Running Macro/VBA in multiple worksheets.
 

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
--
SHD


"Gary L Brown" wrote:

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


HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"YellowBird" wrote:

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.

--
SHD

Running Macro/VBA in multiple worksheets.
 

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.
--
SHD


"Bob Phillips" wrote:

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)

"YellowBird" wrote in message
...
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.

--
SHD

Running Macro/VBA in multiple worksheets.
 

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)

"YellowBird" wrote in message
...
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.
--
SHD


"Bob Phillips" wrote:

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)

"YellowBird" wrote in message
...
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.

--
SHD

Running Macro/VBA in multiple worksheets.
 

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.

--
SHD


"Bob Phillips" wrote:

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)

"YellowBird" wrote in message
...
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.
--
SHD


"Bob Phillips" wrote:

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)

"YellowBird" wrote in message
...
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.

--
SHD

Running Macro/VBA in multiple worksheets.
 

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

--
Regards,
Tom Ogilvy


"YellowBird" wrote in message
...
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.

--
SHD


"Bob Phillips" wrote:

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)

"YellowBird" wrote in message
...
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.
--
SHD


"Bob Phillips" wrote:

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)

"YellowBird" wrote in message
...
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.

--
SHD

Running Macro/VBA in multiple worksheets.
 

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.
--
SHD


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"YellowBird" wrote in message
...
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.

--
SHD


"Bob Phillips" wrote:

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)

"YellowBird" wrote in message
...
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.
--
SHD


"Bob Phillips" wrote:

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)

"YellowBird" wrote in message
...
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.

--
SHD

Running Macro/VBA in multiple worksheets.
 

Yes it should be Activeworkbook or ThisWorkbook depending.

Sometimes I make typos. Glad you got the point.

--
regards,
Tom Ogilvy




"YellowBird" wrote in message
...
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.
--
SHD


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"YellowBird" wrote in message
...
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.

--
SHD


"Bob Phillips" wrote:

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)

"YellowBird" wrote in message
...
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.
--
SHD


"Bob Phillips" wrote:

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)

"YellowBird" wrote in
message
...
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.

--
SHD