Forum Discussion
Auto Refresh for Pivot tables created on sheets (Master data also on the same sheet)
Click in a pivot table.
Activate the PivotTable Analyze tab of the ribbon.
Click 'Change Data Source'.
Either type the name of the table, or click in the box, then activate the sheet with the source table, and select the entire table. Excel should automatically replace the range address with the name of the table.
Click OK.
See for example 5 Reasons to Use an Excel Table as the Source of a Pivot Table
If the source data are not in a table, convert the range to a table, then set the source range of the pivot tables to that table. That way, new rows added to the table will automatically be included in the pivot table source.
Right-click the sheet tab of Sheet 2.
Select View Code from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Activate()
Me.PivotTables(1).PivotCache.Refresh
End SubEach time you switch to Sheet 2 to view the pivot tables, they will automatically be updated.
Save the workbook as a macro-enabled workbook (*.xlsm)
I tried to add the code., but couldn't achieve the expected output.
Herewith attached a draft datasheet. Can you kindly check and support?
Sorry I'm unable to attach the file .
Thanks
If you cannot attach a workbook, please upload it to a cloud service such as Google Drive or OneDrive, obtain a link and post the link in a reply.
Thank you for letting me know.
Please find the link. Hope you can open the file
Thanks
Thanks. See the attached version.
