I'd like to use this macro on many spread sheets. However, this part of the code contains a name
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
"FEB2004_Cook!R10C1:R69C14").CreatePivotTable TableDestination:="",
TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion1
It's calling out the work sheet FEB2004_Cook. I tried activeworksheet and some other ways but to no avail.
Any help
Thanks
DJ

Duncan,

U*ntested, but

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
Activesheet.Name & "!R10C1:R69C14").CreatePivotTable
TableDestination:="", _
TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Duncan J" wrote in message
...
I'd like to use this macro on many spread sheets. However, this part of
the code contains a name.
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"FEB2004_Cook!R10C1:R69C14").CreatePivotTable
TableDestination:="", _
TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
It's calling out the work sheet FEB2004_Cook. I tried activeworksheet and
some other ways but to no avail..
Any help,
Thanks,
DJ

Thanks Bob seems to be working fine...

Thanks A lot!
DJ