PivotTable Source & Destination
 

Dim sh as Worksheet
for each sh in Worksheets(Array("Sheet1","Sheet2","Sheet3"))
sh.Activate
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
Activesheet.Name & "!R1C1:R170C4").CreatePivotTable TableDestination:= _
"'[Alcoa 90-04.xls]" & ActiveSheet.Name & "'!R2C6",
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
Next


--
Regards,
Tom Ogilvy

"EricB" wrote in message
...
I have a question about macros. I have a workbook which contains 50
worksheets; each worksheet contains historical price data for a single
stock
(50 stocks=50 worksheets). I recorded my first ever macro to create a
PivotTable based on this data and saved it in the Personal Macro Workbook.
The goal is to have the macro create a PivotTable on Sheet1 using the data
on
Sheet1, then go to Sheet2, create a PivotTable using the data on Sheet2,
then
go to Sheet3, etc.

The macro works great until it gets ready to read the SourceData when
trying
to create the PivotTable on Sheet2. This code generating an error:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Sheet1!R1C1:R170C4").CreatePivotTable TableDestination:= _
"'[Alcoa 90-04.xls]Sheet1'!R2C6", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10

I suspect the problem is that the SourceData and TableDestination refer to
Sheet1, when the goal is to read the data on Sheet2.

Is there any way to reference the data on Sheet1, then reference the data
on
Sheet2, etc. all the way to Sheet50?

Thanks for your help,
Eric