Detaching Macro from workbook and sheet names

[derek.hughes]

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

 

[VoG]

Try

Sheets(Sheets.Count).Select

 

[derek.hughes]

Thanks for the fast response! I did that, but realized I also get an error here:

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

How do I work around the "table destination" code?

Thanks!

Derek

 

[VoG]

Try

Sheets.Add
s = Sheets(Sheets.Count).Name
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
s & "!R1C1:R98C12", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14
Sheets(s).Select

 

[derek.hughes]

Thanks!