Pivot Refresh Macro

[Aswinraj]

Hi friends,

I need a help in Refreshing the Pivot table using Macro.

In a Excel, there is a Sheet called EPO Compliance, where user will remove all the data and replace it with new data.

Once its done, User will go to Home page and click Refresh Pivot. Once it was clicked, it should replace all the 9 Sheets from EPO - 1 to EPO - 9.

I tried some codings which doesnt work.. Can you please help me.., Those codes which i tried was also in the sheet.

 

[Chihiro]

Try something like below.

Dim ws As Worksheet
Dim pvt As PivotTable
For Each ws In ThisWorkbook.Worksheets
    For Each pvt In ws.PivotTables
        pvt.RefreshTable
        pvt.Update
    Next pvt
Next ws

 

[Chihiro]

Oh, by the way. You have fixed range for PivotTables. EX: 'EPO Compliance'!$A$2:$AT$156

In order to update with dynamic range. Do one of the following:
1. Set up Named Range with Offset() to adjust range and use that for your PivotTable data source.
2. Set up EPO Compliance as Excel Table and use table name as PivotTable data source.

 

[Aswinraj]

Apologies, i dont know how to do that., Is it possible for you to modify the coding as needed in the excel file which had attached. Please..

 

[Chihiro]

Hm? It's not the coding but PivotTable Data Range you need to edit.

While range can be adjusted via code. It's not recommended as all Pivot seems to share same source (it will be faster once initial set up is done).

I'll be bit busy for next couple of hours.

Follow steps in link and see if you can do it yourself (use Option1 or 3).
http://www.contextures.com/xlPivot01.html

If still unable to resolve, I'll check again later in the day.

 

[Aswinraj]

Sure i will check it., and let you know.., Thanks for your patience..,

 

[Chihiro]

See attached. Used dynamic named range for Pivot Data source.