Pivot Tables and a simplied front table (macros?) | Excel forum

Resolved · Low Priority · Version 2010

Thu 16 Nov 2017, 13:18 Delegate Lewis said...

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.

Fri 17 Nov 2017, 10:09 Trainer Jens said...

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

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Line breaks in a cell

You can control the line breaks for multiple-line headings or labels in your Microsoft Excel worksheet, just like you do in Microsoft Word. Here's how to do it.

Click the cell where you want the label or heading to appear.
Type the first line of information.
Press ALT+ENTER.

View all Excel hints and tips