Link to home
Start Free TrialLog in
Avatar of Mike French
Mike FrenchFlag for United States of America

asked on

what is causing the error?

I am trying to update the source data for two pivot tables on the same worksheet. I am getting an invalid procedure call error. the code I am attempting use is below.

Dim pt As PivotTable
Dim SalesData As Range, lRow As Long lRow = Worksheets("Sales Data").Range("A1000000").End(xlUp).row 'Worksheets("Sales Data").Range("A1:N" & lRow).Name = "SalesData" Set SalesData = Range("SalesData")     For Each pt In Worksheets("Assistance").PivotTables          pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _             (SourceType:=xlDatabase, SourceData:=SalesData) Next pt

Open in new window

Avatar of CompProbSolv
Flag of United States of America image

Which line is giving you the error?  If not clear, set a breakpoint at the beginning and step through it.
Does the named range "SalesData" contain proper column headers? Make sure no column header is blank and see if it works then.
Transform your range into a data table, set the pivot table's data source point to the data table, done !
You'll never ever need to touch the pivot table's data source ever again.
Avatar of Mike French


Thanks guys,

The problem seems that the code doesn't work while the two pivot tables are connected to a timeline slicer. When I disconnected them from the timeline the code works fine. The problem now is that the timeline available report connections only show one of the pivot tables although both have the same range as the source data? Can you tell me how to fix that?

Mind uploading a sample workbook ?
Avatar of Fabrice Lambert
Fabrice Lambert
Flag of France image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial