Using a macro over a number of sheets
 

I have this small macro that i recorded that does a few small functions
including copying & pasting a small bit of info from one sheet onto another
sheet. The thing is that I have to do this to about 50 sheets. Is there a
way to have the macro jump to the next sheet and do the same thing
automatically?

Using a macro over a number of sheets
 

Put this code around your macro code:

For Each Sheet In Worksheets
Sheet.Activate
' Your code here

Next Sheet

If you want it to miss out the first sheet, use this:

For Each Sheet In Worksheets
if Sheet.Name < "Sheet1" Then
Sheet.Activate
' Your code
Endif
Next Sheet

The name to check for is the name on the sheet tab.

HTH
Helen


-----Original Message-----
I have this small macro that i recorded that does a few
small functions
including copying & pasting a small bit of info from one
sheet onto another
sheet. The thing is that I have to do this to about 50
sheets. Is there a
way to have the macro jump to the next sheet and do the
same thing
automatically?


.

Using a macro over a number of sheets
 

Hi
try something like

dim wks as worksheet
for each wks in worksheets
'your code
next


--
Regards
Frank Kabel
Frankfurt, Germany

"Dominique Feteau" schrieb im Newsbeitrag
...
I have this small macro that i recorded that does a few small
functions
including copying & pasting a small bit of info from one sheet onto
another
sheet. The thing is that I have to do this to about 50 sheets. Is
there a
way to have the macro jump to the next sheet and do the same thing
automatically?

Using a macro over a number of sheets
 

for a better answer, you need to post your code with a clearer explanation
of what you want to achieve. You imply that the code is moving between
sheets now, using either of the solutions offered may work or they may not
depending on how your macro determines where to paste the information. It
sounds like you might want some type of consolidation macro, in which case,
just wrapping a loop around your existing code might not be the whole
solution.

--
Regards,
Tom Ogilvy

"Dominique Feteau" wrote in message
...
I have this small macro that i recorded that does a few small functions
including copying & pasting a small bit of info from one sheet onto
another
sheet. The thing is that I have to do this to about 50 sheets. Is there
a
way to have the macro jump to the next sheet and do the same thing
automatically?

Using a macro over a number of sheets (almost)
 

Thanks Helen

That works but maybe i should give u a little bit more info on my code.
What its doing is copying some info from one sheet in one workbook, going to
another open workbook, inserting a new worksheet, a message box opens so i
can input the name of that new worksheet, and finally pasting that copied
info onto the new sheet. I tried what you told me, but it wasnt changing the
sheet. Heres the code:

Sub Access()


Dim RenamSheet As String

'here is where it copies assuming that workbook and sheet i have copied
is selected

Range("B26:M28").Select
Selection.Copy
Windows("Access.xls").Activate

'add the new sheet and rename it

Sheets.Add
RenamSheet = InputBox("Rename Sheet")
ActiveSheet.Name = RenamSheet
Range("C1").Select

'here is where it pastes that new info along with some other formatting

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=True
Range("B1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "January"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B12"), Type:=xlFillDefault
Range("B1:B12").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "Annual Subscription Fees"
Range("A2").Select
Columns("A:A").EntireColumn.AutoFit
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A12"), Type:=xlFillDefault
Range("A1:A12").Select
Range("A13").Select
ActiveCell.FormulaR1C1 = "Consultative Support"
Range("A13").Select
Selection.AutoFill Destination:=Range("A13:A24"), Type:=xlFillDefault
Range("A13:A24").Select
Range("A25").Select
ActiveCell.FormulaR1C1 = "Production"
Range("A25").Select
Selection.AutoFill Destination:=Range("A25:A36"), Type:=xlFillDefault
Range("A25:A36").Select
Range("B1:B12").Select
Selection.Copy
Range("B13").Select
ActiveSheet.Paste
Range("B25").Select
ActiveSheet.Paste
Range("D1:D12").Select
Application.CutCopyMode = False
Selection.Cut
Range("C13").Select
ActiveSheet.Paste
Range("E1:E12").Select
Selection.Cut
Range("C25").Select
ActiveSheet.Paste
Range("A1").Select

'then goes back to the original file

Windows("Activebillings2004.xls").Activate


End Sub

"Helen Trim" wrote in message
...

Put this code around your macro code:

For Each Sheet In Worksheets
Sheet.Activate
' Your code here

Next Sheet

If you want it to miss out the first sheet, use this:

For Each Sheet In Worksheets
if Sheet.Name < "Sheet1" Then
Sheet.Activate
' Your code
Endif
Next Sheet

The name to check for is the name on the sheet tab.

HTH
Helen


-----Original Message-----
I have this small macro that i recorded that does a few
small functions
including copying & pasting a small bit of info from one
sheet onto another
sheet. The thing is that I have to do this to about 50
sheets. Is there a
way to have the macro jump to the next sheet and do the
same thing
automatically?


.

Using a macro over a number of sheets
 

i just posted my code in the response to helen trim's solution.

thanks tom

"Tom Ogilvy" wrote in message
...
for a better answer, you need to post your code with a clearer explanation
of what you want to achieve. You imply that the code is moving between
sheets now, using either of the solutions offered may work or they may not
depending on how your macro determines where to paste the information. It
sounds like you might want some type of consolidation macro, in which
case,
just wrapping a loop around your existing code might not be the whole
solution.

--
Regards,
Tom Ogilvy

"Dominique Feteau" wrote in message
...
I have this small macro that i recorded that does a few small functions
including copying & pasting a small bit of info from one sheet onto
another
sheet. The thing is that I have to do this to about 50 sheets. Is
there
a
way to have the macro jump to the next sheet and do the same thing
automatically?

Using a macro over a number of sheets
 

I'll wait quitely and see what Helen responds with.

--
Regards,
Tom Ogilvy

"Dominique Feteau" wrote in message
...
i just posted my code in the response to helen trim's solution.

thanks tom

"Tom Ogilvy" wrote in message
...
for a better answer, you need to post your code with a clearer
explanation
of what you want to achieve. You imply that the code is moving between
sheets now, using either of the solutions offered may work or they may
not
depending on how your macro determines where to paste the information.
It
sounds like you might want some type of consolidation macro, in which
case,
just wrapping a loop around your existing code might not be the whole
solution.

--
Regards,
Tom Ogilvy

"Dominique Feteau" wrote in message
...
I have this small macro that i recorded that does a few small
functions
including copying & pasting a small bit of info from one sheet onto
another
sheet. The thing is that I have to do this to about 50 sheets. Is
there
a
way to have the macro jump to the next sheet and do the same thing
automatically?

Using a macro over a number of sheets
 

Hey Tom

You think u can help me out?

Niq

"Tom Ogilvy" wrote in message
...
I'll wait quitely and see what Helen responds with.

--
Regards,
Tom Ogilvy

"Dominique Feteau" wrote in message
...
i just posted my code in the response to helen trim's solution.

thanks tom

"Tom Ogilvy" wrote in message
...
for a better answer, you need to post your code with a clearer
explanation
of what you want to achieve. You imply that the code is moving
between
sheets now, using either of the solutions offered may work or they may
not
depending on how your macro determines where to paste the information.
It
sounds like you might want some type of consolidation macro, in which
case,
just wrapping a loop around your existing code might not be the whole
solution.

--
Regards,
Tom Ogilvy

"Dominique Feteau" wrote in message
...
I have this small macro that i recorded that does a few small
functions
including copying & pasting a small bit of info from one sheet onto
another
sheet. The thing is that I have to do this to about 50 sheets. Is
there
a
way to have the macro jump to the next sheet and do the same thing
automatically?