Macros don't work before refreshing pivot tables

[Macao]

I have a problem with a macro, which changes pivot table filter values when I choose an item from a drop-down list in another worksheet.


Everytime I open the file, the macro doesn't work before I refresh the pivot tables, and because all underlying data is stored in other Excel files the refreshing takes several minutes.


Is there a way to prevent this and use the macro without refreshing pivot tables first?


Many thanks in beforehand!

 

[Hui]

Macao

Are you enabling macros when Excel loads the workbook ?

In Excel 2007/10 there is a small button underneath the Toolbar when the spreadsheet loads

Excel 2003 should ask do you want to trust the workbook

Can you post the macro code ?

 

[Macao]

I do enable macros, but I have not saved the pivot tables with the underlying data to keep the file size small. I get the following error message when trying to use the PT:


"The PivotTable report was saved without the underlying data. Use the Refresh Data command to update the report."


This refresh takes then 10-15 minutes because there is so much data in several external Excel files. I'm looking for a way to avoid the refreshing part when opening the file, so I can use it as before I closed it previously.


Here's the code:


Private Sub Worksheet_Change(ByVal Target As Range)


Dim ws As Worksheet

Dim pt As PivotTable

Dim pi As PivotItem

Dim strField As String

Dim strField2 As String

Dim strField3 As String


strField = "Product line"

strField2 = "Region"

strField3 = "Customer"


On Error Resume Next

Application.EnableEvents = False

Application.ScreenUpdating = False


If Target.Address = Range("B6").Address Then


For Each ws In ThisWorkbook.Worksheets

For Each pt In ws.PivotTables

With pt.PageFields(strField)

For Each pi In .PivotItems

If pi.Value = Target.Value Then

.CurrentPage = Target.Value

Exit For

Else

.CurrentPage = "(All)"

End If

Next pi

End With

Next pt

Next ws

End If


Application.EnableEvents = True

Application.ScreenUpdating = True


End Sub

 

[Macao]

Sorry for my poor skills, I don't know how to write the code here so that it looks nice as the tab button doesn't work...