Auto update of fields in Pivot table

Hello,

I have a pivot table which shows the previous year current month (Nov'17), the Dec'17, the current month (Nov'18) values for comparison. We need to change both the previous year current month and the current month every month. I have around 15 files with 10 pivot tables in each file to be updated.

In the Pivot table source data, the column containing the previous year current month (Nov'17) gets changed every month. (refer sample file attached)

Can someone help on this by using macro.

Thanks in advance

Regards,
aganesan99

Istead of a macro, try a column of formulas in your source data that return TRUE for the data of interest, and FALSE for everthing else, based on the date associated with the data point, along the lines of

=OR(AND(MONTH(A2)=MONTH(TODAY()),YEAR(A2)=YEAR(TODAY())),AND(MONTH(A2)=MONTH(EDATE(TODAY(),-12)),YEAR(A2)=YEAR(EDATE(TODAY(),-12))),AND(MONTH(A2)=MONTH(EDATE(TODAY(),-11)),YEAR(A2)=YEAR(EDATE(TODAY(),-11))))

(For dates in column A, of course...)

Then use that field as a page filter, and when you refresh the data after a month change, the correct data will be extracted.

Your table is not a database, so you need to unpivot your cross-tab table using a data query. It is very simple to do - if you haven't used Data Queries before it is worth learning.

After you unpivot your current table, then you can apply the logic formula to every row of the new table in a new column, and that will allow you to then use a pivot table with filters to show the desired data.

Here is something to get you going on un-pivoting:

https://www.myexcelonline.com/blog/u...l-power-query/