refer to current file name in PivotCaches.Add

[jo]

I need pass the file name of the open file to the code below where it
says MyCurrentFileName. How do I do this?

I need to create a pivot table based on the data in an open worksheet.
The macro is store in my personal macro area.


ActiveWorkbook.PivotCaches.Add(SourceTy

pe:=xlDatabase, SourceData:= _


"TempSpace!R1C1:R2000C15").CreatePivotT

able TableDestination:= _
"'[MyCurrentFileName.xls]MyPivot'!R1:R65536" _
, TableName:="PivotTable1",

DefaultVersion:=xlPivotTableVersion10

 

[Guest]

if it's the active workbook add
Dim aWB as WOrkbook

Set aWB = ActiveWorkbook


TableDestination:= _
"'[" & aWB.name & "]MyPivot'!R1:R65536" _
, TableName:="PivotTable1",