Using a macro over a number of sheets

  • Thread starter Dominique Feteau
  • Start date
D

Dominique Feteau

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?
 
H

Helen Trim

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
 
F

Frank Kabel

Hi
try something like

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

Tom Ogilvy

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

Dominique Feteau

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
 
D

Dominique Feteau

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

thanks tom
 
Top