derek.hughes
Board Regular
- Joined
- Mar 16, 2012
- Messages
- 53
This is a problem that plagues all my macros, so I'm hoping there's an easy solution.
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R98C12", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14
Sheets("Sheet4").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Inst Name")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Emplid"), "Count of Emplid", xlCount
This is just an example to pose the question, but say I am adding a pivot table from the data in a new sheet. When I recorded the macro the "new sheet" is "Sheet4", but when I run the macro it the "new sheet" may be something like "Sheet7" causing the Macro to need debugging.
Is there a way around this nonsense?
Thanks!
Derek
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R98C12", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14
Sheets("Sheet4").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Inst Name")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Emplid"), "Count of Emplid", xlCount
This is just an example to pose the question, but say I am adding a pivot table from the data in a new sheet. When I recorded the macro the "new sheet" is "Sheet4", but when I run the macro it the "new sheet" may be something like "Sheet7" causing the Macro to need debugging.
Is there a way around this nonsense?
Thanks!
Derek