Macro to push the results of all formulae on multiple worksheets to another workbook

[sraon]

I inherited a price list workbook made up of multiple worksheets (some 30 pages). Each year the new prices (including % increase) are calculated by a formula in the row below each price (100's of rows and formulae).
Thus:
C1 might say £10
D1 says =C1+(C1*2.5%) £10.25 (it actually does some rounding too)
etc
It may seem odd having both rows but MD can see the before and after. The new price 10.25 must however be manually copied to C1 to avoid circular referencing and then D1 row hidden. Very laborious to repeat 100's of times.

Is there a way of first copying the entire workbook for the new year (easy) then by means of a macro looping through each formula cell of all sheets in workbook 1 and pushing the result to the new workbook and to the correct sheet and then to the same location but to the cell above. e.g. pushing £10.25 from D1 of sheet 1 workbook 1 to C1 of sheet 1 workbook 2 to become the new price for the coming year?

There may be entirely better way of managing price lists and it would be interesting to hear how others do it but it would be expedient if I could just get this variant working quickly for now. Thanks.

[Olly]

Please attach a workbook showing how your data is structured, some example data, and what your required outcome looks like.

[sraon]

Hate to ask how to attach a file but I'm a forum rookie!

[Olly]

It's covered in The Rules

Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

[sraon]

thisyear.pnglastyear.pngWIDGETS.xlsx

Please see mock-up workbook 3 pages only. In reality no of sheets variable. See from snapshots of page 1 that results fields of lastyear workbook become source fields for the new thisyear workbook. Same required for all sheets. Problem is I need a macro to do this automatically. To loop through all sheets of active workbook and take each formula result and push it to the same location but the cell above in the thisyear workbook so that it becomes the source cell for future calculations.

Thank you so much.

[Olly]

Okay. Try:

Please Login or Register  to view this content.

[sraon]

Okay. Try:

Please Login or Register  to view this content.

Thanks so much it works for Col C only on the currently active sheet without error, however for my purposes it has the following issues:
1. It only works on Col C when in fact (sorry I didn't send a sheet to illustrate this) there can be a variable number of columns on the sheet containing prices/formulae e.g. B, or C, D, E, or F, H, J, L etc and not necessarily include C. Also variable no of formula rows, or even no prices at all on the intro sheets.
2. This code updates Col C in the current sheet and I was envisaging it involving updating second workbook. But may be I rethink this can copy the Lastyear workbook and run the macro on the Thisyear workbook?
3. Is there a way of ensuring the macro runs on all Price/Formula Cells for ALL sheets in the workbook automatically other than having to highlight and select all Sheets before running the macro. I am nervous that not all prices will be updated and that will cause havoc?
Thank you so much though and I am grateful for your help thus far.

[Olly]

To deal with all columns, change the code to:

Please Login or Register  to view this content.

This will now work on all formula cells, not just column C.
(Advice: state your requirement very clearly, as you will likely get exactly what you ask for!!)

The code already works on ALL worksheets in the workbook (see that it loops through each worksheet in the workbook?)

And yes, the easiest way is to make a copy of the workbook, THEN run the code. You could do this in code, if you needed to, but unless you are doing it many times, it's probably just as quick and easy to take a manual copy.

[sraon]

Thank you that is brilliant. Yes can see I wasn't clear on that no of cols is variable.
Greatly appreciate your speed and helpfulness on this.

[Olly]

Great, thanks for the feedback.

If your question is now answered, can you please mark this thread 'Solved'. You may also wish to add to the reputation of those who helped you, by clicking the small 'Add Reputation' star below their posts.