Hey everyone,

Every time I try and use a macro to create a pivot table, I always get an error message as shown below

I think the problem here is related to the fact that the file name and sheet name always change. This is because I apply the macro to different files that I extract from SAP, and each file has a different name.

This is the part of my VBA code that is identified as problematic in VBE:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sophie & Toffee - Starting File!R1C1:R35150C37", Version:=6). _
CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable2" _
, DefaultVersion:=6
As you can see it is taking as a source data the name of the tab of the file where I have recorded the macro. Is it possible to make it dynamic, or to avoid to consider the file name at all?

Please help me to find a solution to this error. It's killing me I've trying a lot of different codes I've found on the web, but I can't find a solution that works

Thanks in advance for your help!

Andre