pivot table in excel to reference multiple workbooks

[mmr3b9]

Hi,

I wrote a macro that creates a pivot table and a chart.
It works correctly when I run it with the data in the same workbook that I created the macro. But when i try to run the macro with another workbook. It gives me an error.

The error says "reference is not valid."

Sub Macro1()

ThisWorkbook.Names.Add Name:="NewName", _
RefersTo:="=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))", Visible:=True

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"NewName&quot.CreatePivotTable TableDestination:="", TableName:="PivotTable1"

ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1&quot.SmallGrid = False
With ActiveSheet.PivotTables("PivotTable1&quot.PivotFields("sexfmt&quot
.Orientation = xlDataField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1&quot.PivotFields("acc&quot
.Orientation = xlDataField
.Position = 2
End With
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.SetSourceData Source:=Sheets("Sheet2&quot.Range("A3&quot

End Sub

The error in the code is in bold. I am using excel 2000. I want to be able to use the macro for any workbook to create a pivote table. Any help would be appreciated.

Thanks,
Mike

 

[combo]

You can work with SourceType:=xlDatabase when the table is either in the same workbook or other open workbook. In case of closed workbooks this fails.
Instead, use external data source (SourceType:=xlExternal) in the pivot table and point excel file as source. You have to name the data range in the source file, otherwise there will be no table to use.

combo

 

[mmr3b9]

Thanks combo for the response.

I want to be able to work the macro with another open workbook. The macro is in workbook A. And i want to run the macro in workbook B. When i run the macro in B. Thats when the error, "Reference is not valid" shows up.

What specifically in my code do i have to change to make the reference valid.

Thanks

Mike

 

[combo]

When you refer to sheet or range without specyfying full path, excel assumes that you refer to active workbook or active path. To fix the parent, rather assign a full sheet reference to variable:
[tt]Dim shData as worksheet, shPivot as worksheet
' set full reference paths to sheets, for instance:
set shData=thisworkbook.worksheets("Sheet1&quot
set shPivot=workbooks(name or index).worksheets(1)
' and create table:
shPivot.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=shData.Range("NewName&quot).CreatePivotTable TableDestination:=shPivot.Range("A1&quot,TableName:="PivotTable1"
' etc.[/tt]

combo