Need help with pivot Table Macro in 97

[jim37055]

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
:

 

[Tom Ogilvy]

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.

 

[jim37055]

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