Playing back a Pivot Table macros

[menken.john]

I’m using Excel 2010 and I have some VBA code that does the following things.
• Makes a copy of sheet 1 and calls it MySheet.
• Inserts a column and does several calculations in MySheet.
• Attempts to perform a pivot table from the data in MySheet and place this Pivot table on a new and separate sheet.
It is this last step with which I am having trouble. I recorded the Pivot table macro manually but upon playback it always breaks on the second line below. It is the line that begins with the words ActiveWorkbook.PivotCaches.Can someone tell me what I’m doing wrong? Thank you.

Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"MySheet!R1C1:R35C4", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet27!R3C1", TableName:="PivotTable2", DefaultVersion _
:=xlPivotTableVersion14
Sheets("Sheet27").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("CC_DD")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Hours"), "Sum of Hours", xlSum
Sheets("Sheet25").Select
Sheets("Sheet25").Move After:=Sheets(3)
Sheets("Sheet25").Select
Sheets("Sheet25").Name = "Pivot Table"

 

[Ben McClave]

Hello,

I think that the problem may lie in the TableDestination argument. If "Sheet27" already exists in the workbook, then you may be trying to overwrite an existing pivottable. Since you want it to go into a new sheet, you couldadd "Dim strDest" to the beginning of your sub and place this line just after your "Sheets.Add" line:

strDest = "Sheet" & Sheets.Count + 1 & "!R3C1"

Finally, change the "TableDestination:="Sheet27!R3C1"" argument to "TableDestination:=strDest". What this will do is ensure that the PivotTable destination is always a new worksheet.

Hope this helps,
Ben

 

[Help]

Thank you. I will give it a try.