Macro to move to next worksheet and repeat task

**wakisme** · 12-05-2012, 11:47 PM

Hi
I am new to Macro and would like some help. Thanks
I have recorded a macro to pick up data from various cells in a monthly report spreadsheet and return them to my annual spreadsheet.
The code does work on the first spreadsheet but I have 13 sheets to get the data from.
I figured there must be a way to have the macro go to the next spreadsheet and pick up the same cells and return them on to the next line.
So after the code has worked on sheet 1 I need to go to sheet 2 and sheet 3 etc and pick up data from the same cells and enter on the next available row (which would be 401, 402, 403 etc)in my annual spreadsheet
Any help would be appreciated.

Thanks very much


Sub example()
'
' Example Macro
'


Range("A400").Select
ActiveCell.FormulaR1C1 = "='[Book1.xls]" & Workbooks("Book1.xls").Sheets(1).Name & "'!R[-395]C[8]"
Range("B400").Select
ActiveCell.FormulaR1C1 = "='[Book1.xls]" & Workbooks("Book1.xls").Sheets(1).Name & "'!R[-397]C[7]"
Range("D400").Select
ActiveCell.FormulaR1C1 = "='[Book1.xls]" & Workbooks("Book1.xls").Sheets(1).Name & "'!R[-393]C[5]"
Range("e400").Select
ActiveCell.FormulaR1C1 = "='[Book1.xls]" & Workbooks("Book1.xls").Sheets(1).Name & "'!R[-392]c[-1]"
Range("j400").Select
ActiveCell.FormulaR1C1 = "='[Book1.xls]" & Workbooks("Book1.xls").Sheets(1).Name & "'!R[-361]c[-6]"
Range("k400").Select
ActiveCell.FormulaR1C1 = "='[Book1.xls]" & Workbooks("Book1.xls").Sheets(1).Name & "'!R[-394]c[-7]"
Range("l400").Select
ActiveCell.FormulaR1C1 = "='[Book1.xls]" & Workbooks("Book1.xls").Sheets(1).Name & "'!R[-393]c[-8]"
Range("m400").Select
ActiveCell.FormulaR1C1 = "='[Book1.xls]" & Workbooks("Book1.xls").Sheets(1).Name & "'!R[-391]c[-4]"
Range("n400").Select
ActiveCell.FormulaR1C1 = "='[Book1.xls]" & Workbooks("Book1.xls").Sheets(1).Name & "'!R[-378]c[-5]"
Range("o400").Select
ActiveCell.FormulaR1C1 = "='[Book1.xls]" & Workbooks("Book1.xls").Sheets(1).Name & "'!R[-376]c[-6]"
Range("p400").Select
ActiveCell.FormulaR1C1 = "='[Book1.xls]" & Workbooks("Book1.xls").Sheets(1).Name & "'!R[-366]c[-7]"
Range("q400").Select
ActiveCell.FormulaR1C1 = "='[Book1.xls]" & Workbooks("Book1.xls").Sheets(1).Name & "'!R[-370]c[-8]"
Range("r400").Select
ActiveCell.FormulaR1C1 = "='[Book1.xls]" & Workbooks("Book1.xls").Sheets(1).Name & "'!R[-374]c[-9]/'[Book1.xls]" & Workbooks("Book1.xls").Sheets(1).Name & "'!R[-392]c[-14]"
Range("s400").Select
ActiveCell.FormulaR1C1 = "='[Book1.xls]" & Workbooks("Book1.xls").Sheets(1).Name & "'!R[-361]c[-10]"
Range("t400").Select
ActiveCell.FormulaR1C1 = "='[Book1.xls]" & Workbooks("Book1.xls").Sheets(1).Name & "'!R[-353]c[-18]"
Range("u400").Select
ActiveCell.FormulaR1C1 = "='[Book1.xls]" & Workbooks("Book1.xls").Sheets(1).Name & "'!R[-353]c[-18]"
Range("v400").Select
ActiveCell.FormulaR1C1 = "='[Book1.xls]" & Workbooks("Book1.xls").Sheets(1).Name & "'!R[-353]c[-18]"
Range("y400").Select
ActiveCell.FormulaR1C1 = "='[Book1.xls]" & Workbooks("Book1.xls").Sheets(1).Name & "'!R[-354]c[-23]"
'
End Sub

**Alf** · 12-08-2012, 11:44 AM

Perhaps something like this

Sub LoopSheet()

Dim i As Intger

For i = 1 To Sheets.Count

Sheets(i).Activat

' your code

Next i

End Sub

Alf

**wakisme** · 12-16-2012, 07:36 PM

Hi thanks for your help. I have used Daves advice as it seemed to work better for me this time but I am hoping to be able to use your code help with a different task.

**davesexcel** · 12-08-2012, 12:13 PM

Hi Wakisme,
Open the two attached workbooks and run the code from the Wakisme workbook.

It is a very simple example using just 3 cells on three sheets.