SOLVED

Auto Refresh for Pivot tables created on sheets (Master data also on the same sheet)

Copper Contributor

Hi

 

Kindly suggest me a way to refresh the pivot tables created in excel automatically

 

Sheet 1 - Main Data (We update daily)

Sheet 2 - Pivot tables

 

How can I refresh the pivot tables on sheet 2 when we add new rows to sheet 1.

 

Many Thanks

 

 

 

 

13 Replies

@Chris525 

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 Sub

Each 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)

@Hans Vogelaar 

I tried to add the code., but couldn't achieve the expected output.

Herewith attached a draft datasheet. Can you kindly check and support?

Chris525_0-1711966752281.png

 

 

Chris525_1-1711966767426.png

 

Sorry I'm unable to attach the file .

 

Thanks

@Chris525 

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.

@Chris525 

Thanks. See the attached version.

@Hans Vogelaar 

 

Many thanks. It works now.

 

Please let me know which step I missed?

 

Thanks

@Chris525 

I converted the source range to a table, and set the pivot tables to refer to the table instead of to a fixed range,

@Hans Vogelaar 

 

Thank you!!

 

Sorry one more question how did you set the pivot tables to refer to the table instead of to a fixed range?

 

Can you kindly suggest me any tutorials I can refer regarding this subject?

 

Thanks

 

best response confirmed by Chris525 (Copper Contributor)
Solution

@Chris525 

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 

@Hans Vogelaar 

 

Another question, the file was already uploaded and shared on SharePoint with others. How can I make the changes (Those pivot tables) and save as macro file?

 

Please advice.

 

Thanks

@Chris525 

I'm not an expert on SharePoint, I cannot help you with that. Sorry!

1 best response

Accepted Solutions
best response confirmed by Chris525 (Copper Contributor)
Solution

@Chris525 

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 

View solution in original post