code for updating a pivot table

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
hi there,

i have a dynamic range that is the range for a pivot table.

is there a way to us vb code to update this pivot table with this changing range? should you define a named range and then update it or something....i actually have no clue where to start on this one.

any help/advice would be great.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
The easiest thing to do is to have the pivot table reference from the column headers to the last row..this works if the "dynamic" you refer to is the number of records (rather than changing headers). Click on the Pivot Table Wizard when the cursor is in the pivot table, then use the back button to get to the data selection screen, and reselect the data to include rows to the bottom of the spreadsheet.

I read somewhere that this slows down the workbook...but to be honest I have yet to see any kind of performance issues and I use this trick all the time.

Is this going to work for you?

Regards
 

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
that is exactly what i need done....but i was hoping to have this in vb code rather than going throught the motions you described.

any ideas?
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
This code will do what you are asking:
Code:
Sub RefreshPT()
Dim pvt As PivotTable

'ReFresh Named Range on Sheet1 to Current Region
    ActiveWorkbook.Names.Add Name:="PRange", RefersTo:=Worksheets("Sheet1").Range("B4").CurrentRegion

'ReFresh Pivot Table on Sheet2
    For Each pvt In Worksheets("Sheet2").PivotTables
        pvt.PivotCache.Refresh
    Next pvt
End Sub
Assign your Pivot table to the Named Range PRange, then run the code to refresh both the Named Range and the Pivot table.
 

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
thanks this works great......

is there a way to have this code run automatically run when the file.xls is opened rather than running the macro?

thank you so much.
Tez
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
Change the name of the macro from "RefreshPT" to "Auto_Open".
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Open the Visual Basic Editor (Alt + F11)
Select your project
Expand to see Microsoft Excel Objects and double-click the ThisWorkbook Object
In the Code Window Paste this:

Code:
Private Sub Workbook_Open()
Call RefreshPT
End Sub
This calls the same macro as above. I usually keep the macro in a standard module.

Rather than calling the macro, you can just paste the macro itself in instead --
Code:
Private Sub Workbook_Open()

'ReFresh Named Range on Sheet1 to Current Region
    ActiveWorkbook.Names.Add Name:="PRange", RefersTo:=Worksheets("Sheet1").Range("B4").CurrentRegion

'ReFresh Pivot Table on Sheet2
    For Each pvt In Worksheets("Sheet2").PivotTables
        pvt.PivotCache.Refresh
    Next pvt

End Sub
 

Forum statistics

Threads
1,181,363
Messages
5,929,538
Members
436,676
Latest member
Mavri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top