Trouble updating dynamic pivot tables

B

bwmustang13

New Member
Joined
Aug 22, 2014
Messages
9
I have a sheet called “Pasted Data” that is dynamic although it will always have the same number of columns the rows change. I am trying to get all 5 of my pivot tables in my sheet labeled, “Pivot Tables” to update. I am trying to get my pivot table to update automatically every time my source data is updated. I have tried to use the refresh commands (ctrl+alt+5 and the refresh button) but it does not seem to work. I want to use a VBA update command to do it for me. How can I do this?</SPAN>
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
hello

What about giving the data a defined name, say "PT_Data", and have all the pivot tables use that defined name as their data source. Then a simple refresh.

So the VBA could be like

worksheets("Pasted Data").range("A1").currentregion.name = "PT_Data"
thisworkbook.refreshall

Better would be to use the "Pasted Data" worksheets code name. Is that familiar? Whatever the code name is, the code would become

wks_pasted_data.range("A1").currentregion.name = "PT_Data"
thisworkbook.refreshall

To way to change the source data for the pivot table might be version dependent. (After creating a defined name PT_Data on the source data,) Try from a pivot table ALT-D-P and then choose back to change the data source.

HTH
 
Upvote 0
Fazza</SPAN>

Thanks for the quick reply, but it still does not seem to work. I labeled the data source in the “Pasted Data” worksheet, “PT_data”. This data range is from cells A2:FB1003. I can get the “Pasted Data” worksheet to update but the pivot tables in the “Pivot Tables” worksheet are not updating. I named my 4 pivot tables, “Price Summary Breakdown”, “Cost Summary by Option”, “Cost Summary by CLIN” and “Labor and Material”. </SPAN>

The dropdown selections that I chose are: Worksheet and SelectionChange</SPAN>

Should I be using PivotTableUpdate? Or some other refernce?</SPAN>

The code I have used is:</SPAN>
Private Sub Worksheet_SelectionChange(ByVal Target As Range)</SPAN>
Worksheets("Pasted Data").Range("A2:FB1003").CurrentRegion.Name = "PT_Data"</SPAN>
ThisWorkbook.RefreshAll</SPAN>
End Sub</SPAN>
 
Upvote 0
A couple of things that I'm not clear on, so I'll mention to be sure we're aligned.

The data range A2:FB1003. When I see row 2 I assume that row 1 is not used at all. Row 2 has the headers. Conventionally row 1 has headers. I need to understand this cause the VBA I posted assumed headers in row 1 & data immediately under. If you have something different the VBA will need to change. What it changes to will depend on your set up - such as if row 1 is empty or not. Please confirm/clarify. [This is all so that the resultant defined name has both the headers & data & nothing else.]

The pivot tables you say don't update. I don't know if you did the step to set the pivot tables' source data to be the defined name. Please advise. That is, it isn't enough to just create a defined name for the source data (when the previously created pivot tables are not set up as using that defined range as their data source): the pivot tables have to have that defined name range specified as their data source. If that has been done, the update should happen.

Not sure exactly what the set up is. For me I normally use a worksheet change event on the data worksheet so that deactivation of the data sheet updates the named range & pivot table. For simple stuff this should be fine. If you've some fancier data updating it might not. So in the code module for the data sheet, the code would be as below

OK?

regards
Code: 
Private Sub Worksheet_Deactivate()
Me.Range("A1").CurrentRegion.Name = "PT_Data"
ThisWorkbook.RefreshAll
End Sub
 
Upvote 0
Yes, row 2 is where the headers are and the data follows beginning on row 2. I set row 1003 as a standard boundary because other formulas on other sheets use it. As for the update, I tried your suggestion at first using the following code.</SPAN>

worksheets("Pasted Data").range("A1").currentregion.name = "PT_Data"
thisworkbook.refreshall</SPAN>

This worked originally but I could not get it to work once I added it to my other code. I am pasting the full code below. As you will be a to see I used a series of macros to put it all together. This may be the problem. What I am trying to do is allow cells A1:EI1003 be dynamically updated by the user. Then I want to add cells EJ2:FB1003 with certain formulas. This will all be done in the worksheet labeled, “Pasted Data”. I am using this to update the “Pivot Tables” worksheet. </SPAN>

Sorry for my ignorance, VBA is not my Excel strength. Thanks for all the help!</SPAN>

Sub MacroTest_ALL_17()</SPAN>
'</SPAN>
' MacroTest_ALL_17 Macro</SPAN>
' Adds all 17 additional formulas to the pasted data.</SPAN>
'</SPAN>
' Keyboard Shortcut: Ctrl+Shift+Y</SPAN>
'</SPAN>
Range("EJ2").Select</SPAN>
ActiveCell.FormulaR1C1 = "MONTH#"</SPAN>
Range("EJ3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-62],lookupTables!months,2,FALSE)"</SPAN>
Range("EK2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
Range("EK2").Select</SPAN>
ActiveCell.FormulaR1C1 = "YEAR#"</SPAN>
Range("EK3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=VALUE(RC[-64])"</SPAN>
Range("EL2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "FISCAL YEAR"</SPAN>
Range("EL3").Select</SPAN>
ActiveCell.FormulaR1C1 = _</SPAN>
"=IF(fyStart>1,IF(RC[-2]>=fyStart,RC[-65]+1,RC[-65]+0),RC[-65]+0)"</SPAN>
Range("EM2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "CALEN QTR"</SPAN>
Range("EM3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],lookupTables!qtrs,3,FALSE)"</SPAN>
Range("EN2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "FISCAL QTR =VLOOKUP(EJ3,qtrs,4,FALSE)"</SPAN>
Range("EN2").Select</SPAN>
ActiveCell.FormulaR1C1 = "FISCAL QTR"</SPAN>
Range("EN3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],lookupTables!qtrs,4,FALSE)"</SPAN>
Range("EO2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "RES/SUB RES"</SPAN>
Range("EO3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=RC[-136]&RC[-135]"</SPAN>
Range("EP2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "BU/BURDEN"</SPAN>
Range("EP3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=RC[-139]&RC[-138]"</SPAN>
Range("EQ2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "BU/BC/RES/SUBRES"</SPAN>
Range("EQ3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=RC[-140]&RC[-139]&RC[-11]"</SPAN>
Range("ER2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "FTE"</SPAN>
Range("ER3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=RC[-64]/mmc"</SPAN>
Range("ES2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "Loaded Labor Through COM"</SPAN>
Range("ES3").Select</SPAN>
ActiveCell.FormulaR1C1 = _</SPAN>
"=IF(RC15=R1C149,SUM(RC[-64],RC[-63],RC[-62],RC[-57],RC[-49],RC[-44]),)"</SPAN>
Range("ET2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "Loaded ODC Through COM"</SPAN>
Range("ET3").Select</SPAN>
ActiveCell.FormulaR1C1 = _</SPAN>
"=IF(RC15=R1C150,SUM(RC[-60],RC[-58],RC[-50],RC[-46],RC[-45]),)"</SPAN>
Range("EU2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "Total COM"</SPAN>
Range("EU3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=RC[-51]+RC[-49]+RC[-47]+RC[-46]"</SPAN>
Range("EV2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "TCL + COM"</SPAN>
Range("EV3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=RC[-57]+RC[-1]"</SPAN>
Range("EW2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "CLINSLIN"</SPAN>
Range("EW3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=IF(RC[-124]<>R1C153,RC[-124]&RC[-123],0)"</SPAN>
Range("EX2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "Option Desc"</SPAN>
Range("EX3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC4,optDescription,2,FALSE)"</SPAN>
Range("EY2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "WBS Desc"</SPAN>
Range("EY3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC5,wbsDescription,2,FALSE)"</SPAN>
Range("EZ2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "CLIN Desc"</SPAN>
Range("EZ3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC153,clinslindescription,4,FALSE)"</SPAN>
Range("FA2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "Site Desc"</SPAN>
Range("FA3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC146,siteDescription,2,FALSE)"</SPAN>
Range("FB2").Select</SPAN>
Selection.NumberFormat = "@"</SPAN>
ActiveCell.FormulaR1C1 = "RES/SUBRes Desc"</SPAN>
Range("FB3").Select</SPAN>
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC145,lgDescription,2,FALSE)"</SPAN>
Range("FB4").Select</SPAN>
Range("ES1").Select</SPAN>
ActiveCell.FormulaR1C1 = "LAB"</SPAN>
Range("ET1").Select</SPAN>
ActiveCell.FormulaR1C1 = "ODC1"</SPAN>
Range("ES2").Select</SPAN>
Range("EJ3:FB3").Select</SPAN>
Selection.AutoFill Destination:=Range("EJ3:FB1003")</SPAN>
Range("EJ3:FB1003").Select</SPAN>
End Sub</SPAN>

Private Sub Worksheet_SelectionChange(ByVal Target As Range)</SPAN>
Worksheets("Pasted Data").Range("A2:FB1003").CurrentRegion.Name = "PT_Data"</SPAN>
ThisWorkbook.RefreshAll</SPAN>
End Sub</SPAN>
 
Upvote 0
For a fixed range of source data A2:FB1003 the code would be

code_name_of_worksheet.range("A2:FB1003").name = "PT_Data"
thisworkbook.refreshall
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question
Pivot table Refresh from a change to data validation list change
Replies
2
Views
141
wisemank
W
B
  • Question
Update Pivot Tables with Refresh
Replies
1
Views
132
shift-del
shift-del
L
  • Solved
Code in excel VBA to refresh SAS tables instead of pivot tables
Replies
2
Views
93
Luigiportucale
L
A
  • Question
Two dimensional dynamic named range over a pivot table not working as I thought
Replies
4
Views
274
ashleighbrown
A
M
  • Question
Excel pivot table query - retaining filters on pivot in absence of data
2
Replies
10
Views
1K
Alex Blakenburg
A

Forum statistics

Threads
1,214,854
Messages
6,121,941
Members
449,056
Latest member
denissimo

Share this page

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
Back
Top