Make name Generic

[Duncan J]

I'd like to use this macro on many spread sheets. However, this part of the code contains a name
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, 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

 

[Bob Phillips]

Duncan,

U*ntested, but

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, 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)

I'd like to use this macro on many spread sheets. However, this part of the code contains a name.
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, 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..

 

[Duncan J]

Thanks Bob seems to be working fine...

Thanks A lot!
DJ