Retain pivot table data every time I refresh it?

Hi everyone,

I have made a pivot table which summarises the number of accounts in different sheets and the dollar value of each sheet.

However, the number of accounts on each sheet (and therefore dollar values) will change on a daily basis, so I 'refresh' my pivot table at the end of each day. But this only gives me the new values and does not retain the values that were there before.

Are there any macros available that would copy a set of the original pivot table data each time I click refresh, so that by the end of the week (for example) I have a set of data for each day? The historical data can just be copied alongside my pivot table.


Currently I use this code-- A1:D23 is my actual range. However, with this code I need to manually copy the previous day's data from G1:J23 to K1:N23 so that today's data can be copied to G1:J23 and this manual copy/pasting is time-consuming as I've got a lot of pivot tables, one for each department.

Please Login or Register  to view this content.

I am quite new to VBA, so any help is greatly appreciated. Thank you

Hi,

Unfortunately your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

I'll change it for you on this occasion since you're new here but please note for the future.

Yes you can of course copy data from one area to another with code like

Please Login or Register  to view this content.

But can we step back a bit because I think you may be missing the whole point about Pivot Tables, and that's that with the click of a drop down filter you can change the PT to reflect whatever you want. Typically you'd filter for a date range to see what the PT looked like last week, last month or whatever. I don't see why you need lots of pivot tables or indeed sheets nor why you need to bother about copying data for historical purposes.

The problem is I believe that you have individual databases on different sheets. You should normalise your database and collect it all on one sheet. Add an extra column to the layout to hold the values that are currently represented by your various sheet names. e.g. if your sheet tab names represent say different departments, just add a column for departments to your single sheet databases and populate that with the department names.