Getting the PivotTable Page data from another sheet

[lynnnow]

Hi,

I've set up a PivotTable and the pivot page data dropdown gets populated from the database. This date changes on a daily basis. Is there a way to automatically set this date to the current date? I tried putting a reference formula for example: In the pivot page field, I put ='Error Log'!A1. However, this kinda causes an overload on the system and the pivot doesn't work well, leading to the workbook crashing.

Can this be done?

Please see the attachment for an example.

Lincoln

[lynnnow]

Bump...

[Bob Phillips]

[vba]

Private Sub Workbook_Open()
On Error Resume Next
With ThisWorkbook

.Worksheets("Pivot").PivotTables("PivotTable1").PivotFields("Date").Current Page = Date
End With
End Sub
[/vba]

[lynnnow]

Thanks xld. I had used a modified version of this code like so:

[VBA]ActiveSheet.PivotTables("PivotTable2").PivotFields("Date").CurrentPage = _
Sheets("Error Log").Range("A1").Value[/VBA]

I put it in the Worksheet_Activate event. However using the code you provided it doesn't work either.

The "current date" in my post at #1 is a date on another sheet (Error Log) that is updated when I need it updated (which is in the date format).

The pivot does not refresh even with the use of
[VBA]ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh[/VBA]

Am I doing something wrong? The pivotcache.refresh line I got from recording the macro. Does it need to be changed to a much swankier line(s) of code?

[Bob Phillips]

The pivot table in your workbook is called PivotTable1.

[lynnnow]

Thanks for pointing that out, missed that. Works gr8.

Thank you xld.

However, please let me know why the code I did would not work. Aren't they the same thing?

[Bob Phillips]

They don't look remotely the same to me. You had a formula (to a non-existent sheet), I had workbook open event code.

[lynnnow]

This is what I have used so far:

[vba]Private Sub Worksheet_Activate()
On Error Resume Next
'ActiveSheet.PivotTables("PivotTable2").PivotFields("Date").CurrentPage = Sheets("Error Log").Range("A1").Value

With ThisWorkbook
.Worksheets("Monthly Locationwise").PivotTables("PivotTable2").PivotFields("Date").CurrentPage = _
Sheets("Error Log").Range("A1").Value
End With

ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
End Sub
[/vba]
The commented one is the one I had used which did not give me the desired result and the working one follows that. In essence I feel that they are the same. The example WB I had provided does not have the sheet names and pivot table as mentioned by the code you provided, but in effect have been changed to suit, yet the code you provided works and the one I tried failed.