Thread: Loop through sheets - pick up formula from same cells each sheet into summary sheet

Hi

I'm a newbie to this forum and hope that someone may be able to provide a solution to what should be a simple problem. I used to have a good working knowledge of VBA some years ago, but haven't used it for ages and am very rusty.

I have a spreadsheet with 53 sheets - one for each week of the year, together with a summary sheet. Each weekly sheet (called, arbitrarily, 01 - 52) has its information in identical cells, reflecting cash flow for that particular week. Totals for each particular product code reside in column fashion in the same cells (say M30..M55) in each weekly sheet.

The summary sheet itemises the totals for each product code on a weekly basis in rows (i.e. one row for each week). Each product code column needs to pick up the formula for its respective week's sheet. I've uploaded a screen dump as a Word doc to try and explain: [VBA Question Screen Dump.doc]

On the summary sheet, cell B8 has the formula: ='02'!M27 which picks up the total for code 3200 from sheet 02. Likewise, E8's formula is: ='02'!M30 which similarly picks up the total for code 3201. And so on. As some items are subject to VAT (tax) and some are not, the gross figures in the summary sheets do not necessarily lie in adjacent columns.

By running the macro recorder (using sheet 2 as an example) I get something like the following:

    Sub TestSummariseManual()
'
' TestSummariseManual Macro
' Macro recorded 11/01/2012 by Paul Webster - manual recorder
'

'
    Range("E8").Select
    ActiveCell.FormulaR1C1 = "='02'!R[22]C[8]"
    Range("H8").Select
    ActiveCell.FormulaR1C1 = "='02'!R[23]C[5]"
    Range("K8").Select
    ActiveCell.FormulaR1C1 = "='02'!R[24]C[2]"
    Range("L8").Select
    ActiveCell.FormulaR1C1 = "='02'!R[25]C[1]"
    Windows("Cash_Analysis_2012 (WITH VBA).xls:1").Activate
    
End Sub

What I need VBA to do is loop through each weekly sheet, pick up the formula(e) for each code and place it in its respective cell for that given week.

Obviously as each week's figures are input to that week's sheet, they will then automatically populate the summary sheet.

I hope I've explained this sufficiently and that someone will be good enough to offer me a solution.

Thanks and regards

Webbo

B7: =INDIRECT("'" & TEXT(A7, "00") &"'!M27")

E7: =INDIRECT("'" & TEXT(A7, "00") &"'!M30")

H7: =INDIRECT("'" & TEXT(A7, "00") &"'!M31")

...etc


Once all the formulas are in for row 7, copy down as far as you've added sheets so far.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.