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.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:D23")) Is Nothing Then
        Application.EnableEvents = False
        Range("K1:N23").Copy Range("G1:J23")    'On Deck becomes Prior Data
        Range("A1:D23").Copy Range("G1:J23")      'New data copied to On Deck area
        Application.EnableEvents = True
    End If
End Sub

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