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

**Webbo** · 01-11-2012, 10:36 PM

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:

Please Login or Register  to view this content.

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

**watersev** · 01-12-2012, 03:40 AM

hi Webbo, welcome to Excelforum. As you have a specific question relating to your workbook and your data layout it would be helpful to provide sample workbook with 3-5 sheets representing weeks with original data layout. Change any sensitive information to something else preserving data format: numbers remain numbers, words remain words. It also helps to see the expected outcome as well for reference.

**Webbo** · 01-12-2012, 09:50 PM

Hi watersev

Thanks for your reply. I've attached a workbook which, hopefully, shows what I'm trying to achieve. I've manually created the formulae for Weeks 1-3 in the "Summary" sheet (Rows 7,8 & 9), which pick up from sheets "01", "02" and "03" respectively. I need to replicate the formulae in Rows 7,8 & 9 in the Summary Sheet to pick up from Sheets 04 - 52 (not yet added).

Hopefully you may be able to point the way! I'm sure the VBA is very simple - I'm just so out of touch. (Even worse - my end user uses Open Office - so once I've run the macro, I have to ensure it works on his platform.)

Thanks and regards

Webbo

**JBeaucaire** · 01-13-2012, 03:07 AM

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.

**Webbo** · 01-13-2012, 08:41 PM

Thanks Jerry - it works perfectly - and without having to use VBA!

Kind regards

Webbo

**JBeaucaire** · 01-13-2012, 09:06 PM

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

**e4excel** · 01-25-2012, 10:35 AM

Hi Webbo,

You can also add this formula in the cells AY7 and AZ7

PHP Code:


AY7=SUM(B7,E7,H7,K7,L7,O7,P7,Q7,R7,U7,X7,Y7,AB7,AC7,AD7,AE7,AF7,AG7,AH7,AI7,AJ7,AK7,AN7,AQ7,AT7,AU7,AV7) 
 
AZ7=SUM(D7,G7,J7,N7,T7,W7,AA7,AM7,AP7,AS7,AX7) 
 
to 
 
AY7==SUMPRODUCT(($B$6:$AX$6="£")*(B7:AX7)) 
 
AZ7=SUMPRODUCT(($B$6:$AX$6="VAT")*(B7:AX7))

Warm Regards
e4excel

**ar2210** · 07-24-2013, 07:14 AM

Hi All,

This is exactly what I want to do, but the only difference is instead of sheets named as 01,02 (i.e numbers), they are product names/text for eg. tea,coffee,milk etc.. And I want my summary sheet to get costs,NPV etc from other worksheets but same cells.

Can anyone suggest please.

Many Thanks in Advance.

Arpita

**ar2210** · 07-24-2013, 08:48 AM

Originally Posted by ar2210

Hi All,

This is exactly what I want to do, but the only difference is instead of sheets named as 01,02 (i.e numbers), they are product names/text for eg. tea,coffee,milk etc.. And I want my summary sheet to get costs,NPV etc from other worksheets but same cells.

Can anyone suggest please.

Many Thanks in Advance.

Arpita

Thanks, Just worked it out!

**Mika0013** · 10-07-2013, 01:43 PM

Originally Posted by ar2210

Thanks, Just worked it out!

Could you please explain how you managed to do this? I'm losing my mind here!

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions