If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Auto refresh of Pivot Table
I found the following code on C. Pearson's website:
Sub Auto_Open() Application.OnSheetActivate = "UpdateIt" End Sub Sub UpdateIt() Dim iP As Integer Application.DisplayAlerts = False For iP = 1 To ActiveSheet.PivotTables.Count ActiveSheet.PivotTables(iP).RefreshTable Next Application.DisplayAlerts = True End Sub However, I cannot make it to work either within a separate module or within "This workbook." Does it perhaps need to be specifically declared as a Public Sub? |
#2
|
|||
|
|||
Auto refresh of Pivot Table
i got the following macro from debra's webiste (contextures.com). i have
tried it and it works for me. Private Sub Workbook_Open() Dim ws As Worksheet Dim pt As PivotTable On Error Resume Next For Each ws In ActiveWorkbook.Worksheets ws.Unprotect Password:="MySecretWord" For Each pt In ws.PivotTables pt.RefreshTable Next ws.Protect DrawingObjects:=True, _ Contents:=True, Scenarios:=True, _ Password:="MySecretWord" Next End Sub --- Message posted from http://www.ExcelForum.com/ |
#3
|
|||
|
|||
Auto refresh of Pivot Table
Do you remember in which section of Debra's website you
found this? It seems that this code unprotects the worksheet, refreshes it and then resets the protection. Is that how it works? -----Original Message----- i got the following macro from debra's webiste (contextures.com). i have tried it and it works for me. Private Sub Workbook_Open() Dim ws As Worksheet Dim pt As PivotTable On Error Resume Next For Each ws In ActiveWorkbook.Worksheets ws.Unprotect Password:="MySecretWord" For Each pt In ws.PivotTables pt.RefreshTable Next ws.Protect DrawingObjects:=True, _ Contents:=True, Scenarios:=True, _ Password:="MySecretWord" Next End Sub --- Message posted from http://www.ExcelForum.com/ . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Pivot table drops columns on refresh | Jason | General Discussion | 1 | July 1st, 2004 02:18 PM |
Probs with creating multiple pivot charts from pivot table | Retreatgal | Charts and Charting | 2 | January 28th, 2004 02:51 AM |
How do I refresh a pivot table | Fred | Charts and Charting | 1 | October 23rd, 2003 05:26 PM |