Macro vs Pivot table problem

[Snoopy]

Hey guy
I have made a copy of my workbook [Order9999] and saved it as
woorkbook [Order02506]
In the workbook I have this singel pivot-table and this singel macro
below:

Sub Ordre9999 ()
Dim DataRange As String
DataRange = Worksheets("Inn i rapporten").Range("A16")
ActiveSheet.PivotTables("Pivottabell2").ChangePivotCache
ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DataRange,
Version:=xlPivotTableVersion12)
End Sub

The macro Ordre9999 function very well.

This macro of course accordingly will be saved in new file along with
saving (as) into the new workbook, and I have afterwords renamed the
new macro to Sub Ordre02596 () - no other changes made.

When doing the new macro it generates an error -message as:

"run time error '-2147024809(80070057)':
Invalid referance"
(transated from norwegian: "referansen er ugyldig")
Though the macro still seems to do its job all-right?

I suspect Excel to "make" a new pivot-table-version in the save-as-
procedure which give my macro some difficulties, but I dont know. I
can find the pivot-table-name ("Pivottabell2"), but I can not find
where to check out the version-code (and I really dont know wether
this is the problem either)... but maybe some of you gyus know - and
want to help me recode the macro?

I wish you all a happy Whitsun holiday

Regards Snoopy

 

[Shane Devenshire]

Hi,

Why are you running this macro at all? When you do File, Save As and rename
the file the reference is to the active workbook?

If for any reason this does not occure automatically when you copy in
Windows, then try selecting the source data and defining it as a table.

If these don't work you need to give us more info, like is the data source
an external reference...