Need help with pivot Table Macro in 97

[jim37055]

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.

[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.

--
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
>

[jim37055]

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