Moving from one column to the next

[segran]

Hi,

I have a workbook with 51 sheets, labelled as week 2 to week 52.
Each sheet captures data for a week in the same cells.

I want to automatically sum a few ranges in the respective sheets into Sheet1.
The sum for the respective rows for Week2 will be in column A, whilst for week3 in column B, and so on.

I recorded a macro for Week2 and Week3:

Sub Macro4()
'
' Macro4 Macro
'

'
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Sheet1").Select ' sheet1 is the sheet i want data summed to
ActiveCell.FormulaR1C1 = "=SUM('Week 2'!R[8]C[24]:R[8]C[28])"
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A9"), Type:=xlFillDefault
Range("A1:A9").Select

Range("B1").Select
ActiveCell.FormulaR1C1 = "=SUM('Week 3'!R[8]C[23]:R[8]C[27])"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B9"), Type:=xlFillDefault
Range("B1:B9").Select
End Sub

How do I do this automatically for all sheet?

Thanking you in advance for your help.

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim c As Long
    For c = 1 To 51
        Cells(1, c).Resize(9).FormulaR1C1 = "=SUM('Week " & c + 1 & "'!R[8]C[" & 25 - c & "]:R[8]C[" & 29 - c & "])"
    Next c
End Sub

 

[segran]

Thank you. It works fantastically well.