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" <jim37055.1wfpmc_1128524744.7744@excelforum-nospam.com> wrote in
message news:jim37055.1wfpmc_1128524744.7744@excelforum-nospam.com...
>
> 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
>