every month, I need to copy the formulas from last month (in the example month P03 on column e.g. X4=+C4 ) into the closing month (in the example month P04 on column Y). I need to then copy paste the value on column X into hard coded value. Then after I refreshed the pivot table, the value for the closing month will be updated in column Y but past periods will stay hard coded with the historical values. Can it be automated without using vba? Thanks
You don't mention how the underlying data is structured, but perhaps you can keep the data for all periods in one large table and then create the pivot table with "Comp" as the row header and "Period" (i.e. P01, P02 etc,) as the column header. Then there is not need to do any copying and pasting. All will update dynamically when you refresh the pivot table.
Thanks Riny for your help! Indeed I had the same thought to have all data listed in the 'input' sheet so there is no need for the client to copy paste anything. I was just wondering maybe there is another way if we don't have all database listed.
Can imagine you could use Power Query to connect all files in a folder that contain the periodic files. Combine them into one large table for the year to date and then create a pivot table from that one. No VBA but it requires a Refresh when the next period's data comes in.
