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

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" <YellowBird@discussions.microsoft.com> wrote in message
news:C1D2BBAC-B1E7-4C93-9775-233243FAFDA4@microsoft.com...
> 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

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


HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
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

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
> gary_brown@ge_NOSPAM.com
> 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

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" <YellowBird@discussions.microsoft.com> wrote in message
> news:C1D2BBAC-B1E7-4C93-9775-233243FAFDA4@microsoft.com...
> > 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
>
>
>

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" <YellowBird@discussions.microsoft.com> wrote in message
news:C1CE73C7-B26D-4E3B-96BC-112393C6DDF4@microsoft.com...
> 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" <YellowBird@discussions.microsoft.com> wrote in message
> > news:C1D2BBAC-B1E7-4C93-9775-233243FAFDA4@microsoft.com...
> > > 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
> >
> >
> >

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" <YellowBird@discussions.microsoft.com> wrote in message
> news:C1CE73C7-B26D-4E3B-96BC-112393C6DDF4@microsoft.com...
> > 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" <YellowBird@discussions.microsoft.com> wrote in message
> > > news:C1D2BBAC-B1E7-4C93-9775-233243FAFDA4@microsoft.com...
> > > > 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
> > >
> > >
> > >
>
>
>

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" <YellowBird@discussions.microsoft.com> wrote in message
news:68D67375-A59C-408B-80DF-EAF6130C9D05@microsoft.com...
> 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" <YellowBird@discussions.microsoft.com> wrote in message
> > news:C1CE73C7-B26D-4E3B-96BC-112393C6DDF4@microsoft.com...
> > > 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" <YellowBird@discussions.microsoft.com> wrote in message
> > > > news:C1D2BBAC-B1E7-4C93-9775-233243FAFDA4@microsoft.com...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >

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" <YellowBird@discussions.microsoft.com> wrote in message
> news:68D67375-A59C-408B-80DF-EAF6130C9D05@microsoft.com...
> > 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" <YellowBird@discussions.microsoft.com> wrote in message
> > > news:C1CE73C7-B26D-4E3B-96BC-112393C6DDF4@microsoft.com...
> > > > 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" <YellowBird@discussions.microsoft.com> wrote in message
> > > > > news:C1D2BBAC-B1E7-4C93-9775-233243FAFDA4@microsoft.com...
> > > > > > 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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Yes it should be Activeworkbook or ThisWorkbook depending.

Sometimes I make typos. Glad you got the point.

--
regards,
Tom Ogilvy




"YellowBird" <YellowBird@discussions.microsoft.com> wrote in message
news:8D13D923-A1B1-43EE-B385-E4686F148C22@microsoft.com...
> 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" <YellowBird@discussions.microsoft.com> wrote in message
> > news:68D67375-A59C-408B-80DF-EAF6130C9D05@microsoft.com...
> > > 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" <YellowBird@discussions.microsoft.com> wrote in message
> > > > news:C1CE73C7-B26D-4E3B-96BC-112393C6DDF4@microsoft.com...
> > > > > 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" <YellowBird@discussions.microsoft.com> wrote in
message
> > > > > > news:C1D2BBAC-B1E7-4C93-9775-233243FAFDA4@microsoft.com...
> > > > > > > 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
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >