refer to current file name in PivotCaches.Add
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
|