I am using Excel 97 to create a second pivot table based on the sam
data as first Pivot table and the macro recorder asks me to specify th
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 name
and can not figure out the proper syntax to reference the source dat
in a file with another name. The easy answer is to use Excel 2003 a
it does not include the workbook name in the macro, but unfortunatel
about half of the people that will be using this macro still are on 97
:

--
jim3705
-----------------------------------------------------------------------
jim37055's Profile: http://www.excelforum.com/member.php...fo&userid=2778
View this thread: http://www.excelforum.com/showthread.php?threadid=47339

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

Thanks for the tip. I was very close, but never got the syntax jus
right

--
jim3705
-----------------------------------------------------------------------
jim37055's Profile: http://www.excelforum.com/member.php...fo&userid=2778
View this thread: http://www.excelforum.com/showthread.php?threadid=47339