Lewis has attended:
Excel Intermediate course
Excel Advanced course
Project Advanced course
Pivot Tables and a simplied front table (macros?)
Hi there
Every week I get a new set of Pivot Tables that have been updated to contain the previous week's costs and hours.
This file name changes each week, as the date the week commences is included in the file name each updated release.
I'd like to create another file that uses the refreshed pivot table file and its information to automatically update the numbers in this new file.
This is because the hours then need to be correlated with work done.
Is this possible? At the moment the best way I can think of is using a macro that adds another column to the table and obtains the data from the Pivot Tables.
And possibly, to ensure there's a consistency to the pivot table format, copy and pasting just the values from the pivot table to a new sheet and then running the macro?
A relatively complex problem, but any ideas?
Thanks in advance.
RE: Pivot Tables and a simplied front table (macros?)
Hi Lewis,
Thank you for the forum question.
What you want cannot be done with a simple recorded macro. You will have to code it using VBA.
A more simple solution could be to have the source data formatted as table (Home tab, Styles group, Format as Table).
If you copy the new column and paste the column in the source table you only have to refresh the PivotTable to access the data from the new column in your PivotTable.
If your formatting change, then it is because you have used the formatting tools from the Home tab to format the PivotTable and not the formatting tools you have under the PivotTable tools.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
