VBA to Recalculate Grand Total

[Steve Belsch]

VBA Experts,

I could use some help. I have attached an example document with the code. In the RunMacroAcrossAllTabs() I am doing the following:

- Run a For Each look to skip the first 8 tabs and then run the code on all other tabs. (in this example document I have only have 2 tabs for simplicity and file size, but in the workbook I am using is 146 tabs)
- The code to create subtotals:
1) Copy paste values for columns A through N, K and S through T
2) Run a Do While loop to organize PO# and then calculate a subtotal of each like value
3) Do some formatting
4) Once loop has executed, delete empty rows

Here is my issue. When I made columns M, N and T into values (which I needed to do so that my formulas in Columns A-T could work) it removes the formula in row 266 in those columns and sets it as a 0 value. Is there a VBA code that can go back and make this a calculation? The row will not stay the same, because I have another macro that deletes blank rows. So it will change from row 266 to something different in every tab. But the columns stay the same.

Any thoughts?

Thanks.
Steve

[Paul_Hossler]

Actually, I got tired of waiting for it to calculate :(

Just for something for you to think about, I made a pivot table version using 'Accual and PO Data' as my input 'data base' (there's a lot more restructuring that could be done)

I believe it's best for complicated applications to not try to do the Presentation as part of the Data

The use of a PT allows a variety of reports / analyses to be put together

In the attachment there's a

Vendor by Project report
Vendor by Year by Project report
Org by Project by Year report

Just something to consider since PTs have many format options and you can create fields and formulas using PT data

I did have to change the PO Date values into a real Excel date

Added thought:

Sometimes I'll make the PT to do the heavy lifting and basic formatting, but a macro to do a special 'presentation quality' report

[Steve Belsch]

Paul,

Yes I can see your point. However, the purpose of this file is forecasting. And there will be many iterations and variations on each tab (146 of them). So, I need something more dynamic and flexible. I think I figured out a workaround.

Thanks for the ideas.

Steve