Avatar of Mike French
Mike French
Flag 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

Microsoft ExcelVBA* Pivot TablesMicrosoft Office

Avatar of undefined
Last Comment
Fabrice Lambert

8/22/2022 - Mon

Which line is giving you the error?  If not clear, set a breakpoint at the beginning and step through it.
Subodh Tiwari (Neeraj)

Does the named range "SalesData" contain proper column headers? Make sure no column header is blank and see if it works then.
Fabrice Lambert

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
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?

Fabrice Lambert

Mind uploading a sample workbook ?
Mike French

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Fabrice Lambert

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question