SourceData:= _
"[JV Open PO's for 10-4-05.xls]Summary!PivotTable1",

the argument is a string. You can make it whatever you want as long as it
is syntactically correct.

bkName = "Myworkbook.xls"

SourceData:="[" & bkname & "]Summary!PivotTable1"

for example.

--
Regards,
Tom Ogilvy


"jim37055" wrote in
message ...

I am using Excel 97 to create a second pivot table based on the same
data as first Pivot table and the macro recorder asks me to specify the
source data. When I do this I get a statement that looks like this...

ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:= _
"[JV Open PO's for 10-4-05.xls]Summary!PivotTable1",
TableDestination:="", _
TableName:="PivotTable3"

The problem is that it includes the name of the workbook in the macro.
I need to be able to run this macro on workbooks with different names
and can not figure out the proper syntax to reference the source data
in a file with another name. The easy answer is to use Excel 2003 as
it does not include the workbook name in the macro, but unfortunately
about half of the people that will be using this macro still are on 97.
:(


--
jim37055
------------------------------------------------------------------------
jim37055's Profile:
http://www.excelforum.com/member.php...o&userid=27788
View this thread: http://www.excelforum.com/showthread...hreadid=473395