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
Microsoft ExcelVBA* Pivot TablesMicrosoft Office
Last Comment
Fabrice Lambert
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.
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
ASKER
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?
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 ?
ASKER
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
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
or
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