• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macros don't work before refreshing pivot tables

Macao

New Member
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!
 
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 ?
 
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
 
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...
 
Back
Top