[CDobyns]

Using Excel 2000, I know that you can select the option to have a pivot table data (cache) update itself whenever the spreadsheet is opened. Is there a similar handy way to have the data (cache) update itself when changes are made to the data fields and say, you shift to the worksheet that contains the pivot table?

I'm guessing that at a minimum I could craft a macro and embed it in button that could be invoked manually, whenever you shift to the worksheet that contains the pivot table, right? Are there other options?

[jimr381]

In the "PivotTable Properties" via the "PivotTable" button on the "PivotTable" toolbar you can change the automatic update property in there.

[CDobyns]

Okay, I believe this answer warrants partial credit, which is usually the maximum amount of credit I seem to receive at work most days anyway . . .

It's correct that you can modify the checkbox under the Pivot Table Options, to select Refresh on Open, under the Data Options, and you can also elect to invoke an auto-refresh after a certain period of time has lapsed for pivot tables that are linked to external tables. However, I don't think you can opt for an "auto-refresh" for a pivot table linked to an internal data source.

I'm looking for the ability to automatically Refresh a pivot table after a modification to a simple internal data source/table. And in this instance the data source and the pivot tables are in the same workbook, but on separate worksheets, and I don't believe there is a way to have that update occur automatically. Or at least that's what my nifty Excel 2002, The Complete Reference book tells me (although books can be wrong . . .).

[slurpee55]

You can have a pivot table auto-refresh IF, and only if, the pivot table is based on external data. And frankly, even then it can be a pain - Excel asks every time if you want to have it enable auto-refresh.

[CDobyns]

Okay, although slurpee55 just restated part of the answer that was already established, I think we'll close this one out and call it solved, since I don't anything else productive will be forthcoming. I think we're generally in agreement that for the situation I've described, the use of a macro, which manually invokes the Refresh is the only option this time.

[slurpee55]

It might be possible to do it using a script, but it would take someone with a lot more knowledge than I have to do that - Firefyter would be who I would ask.

[jimr381]

I will try to drudge up my Excel VBA book I have at home and bring it in tomorrow.

[CDobyns]

Let's save everyone the trouble of extra work. We've already recorded a macro and assigned it to a button on the pivot table worksheet. It seems to be working fine and refreshing the data when invoked. Wish it could be made automatic, but it's there, "right in your face", and if you forget to manually invoke the macro, then you just need to make a resolution to start steering clear of the cheap box wine the night before.

[jimr381]

But cheap box wine will make me feel so highbrow.....

[slurpee55]

Maybe you need some script to have a message box pop up when you open the file that says "Don't forget to press the button!" ?
Ah, box wine...purty colored stuff comes out. But I limit myself to one box per night (or so).

[CDobyns]

All someone is obviously already into their second "Franzia" for a Friday evening. There's no reason to insert any sort of script for a pop-up dialog box reminder, because we've already switched on the auto-refresh on open option - so we're being updated every time we open the file anyway.

[rconverse]

I am having a similar issue. I have a file with four total worksheets, three of them being pivot tables that are created from the data in worksheet 4. I am looking for a way to refresh the tables every time I change the data. I tried recording my own macro, but receive the following error:

1004

"Unable to get the PivotField property of the PivotTable Class."

The code that recorded is below. I am much more familiar with coding in
Access, so don't even know where to start here.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"tempPO_ExpSumm!C1:C4").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable4", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Expeditor")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("LS_Date")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("PO_Type")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable4").AddDataField
ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("PO_Count"), "Count of PO_Count", xlCount

[Zack Barresse]

Perhaps a refresh when you activate the worksheet?

..

http://www.vbaexpress.com/kb/getarticle.php?kb_id=80

HTH

[rconverse]

Originally Posted by firefytr

Perhaps a refresh when you activate the worksheet?

..

http://www.vbaexpress.com/kb/getarticle.php?kb_id=80

HTH

Yes! That will work very well. Thank you very much.

Whoa, I almost forgot my other issue. The other spreadsheet I have is exported from an Access DB that I threw together. I created a macro that runs and does some formatting, etc. The last portion that I am trying to add into the macro is the creation of a new pivot table in a new worksheet. There is some code above that Excel wrote when I recorded the macro, however, when I try to run the macro I get an error (defined above). Any assistance with that would also be very appreciated.

Thank you,

Roger

[mambass]

I have a worksheet where columns D and E contain data that is summed in a pivot table that is contained on the same worksheet. I wanted the pivot table to be automatically updated whenever a value in column D or E was changed. To accomplish this I established a Worksheet_Change event handler for the worksheet by right-clicking the worksheet tab, selecting <View Code> from the popup menu, and then entering the following code in the displayed Microsoft Visual Basic editor window:


Private Sub Worksheet_Change(ByVal Target As Range)
'
' Update the pivot table on this worksheet if data in columns D or E is changed
'
If (Target.Column = 4) Or (Target.Column = 5) Then
ActiveSheet.PivotTables(1).RefreshTable
End If
End Sub

I hope this helps.