Hello,

I have the below code to create a pivot table based off of a data set. It is a template that will be used going forward however the code references Sheets("Sheet33").Select in it. The problem with this is when I test and delete tabs and rerun the code the sheet is not Sheet 33 any longer. What can I put to make it dynamic? I have code that creates two pivot tables and they both have this problem. 


Sub Dept76000Pivot()
'
' Dept76000Pivot Macro
'

'
    Dim LastRow6 As Long
    With Worksheets("76000 Original")
    Sheets("76000 Original").Select
    Sheets.Add
    LastRow6 = .Range("C" & Rows.Count).End(xlUp).Row
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "76000 Original!R4C1:R" & LastRow6 & "C21", Version:=6).CreatePivotTable _
        TableDestination:="Sheet32!R3C1", TableName:="PivotTable2", DefaultVersion _
        :=6
    Sheets("Sheet32").Select
    Cells(3, 1).Select
    End With
    With ActiveSheet.PivotTables("PivotTable2")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable2").RepeatAllLabels xlRepeatLabels
    Sheets("Sheet32").Select
    Sheets("Sheet32").Name = "76000"
    Sheets("76000").Select
    With ActiveWorkbook.Sheets("76000").Tab
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("GL String")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Debit"), "Sum of Debit", xlSum
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Credit"), "Sum of Credit", xlSum
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Company CO")
        .Orientation = xlColumnField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Cost Center")
        .Orientation = xlColumnField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Region")
        .Orientation = xlColumnField
        .Position = 4
    End With
    Sheets("76000").Select
    Sheets("76000").Move After:=Sheets(13)
End Sub