Why won't a macro for a pivot table run on an identical workbook?

[superbuttz]

Hello - I posted a similar question the other day, but maybe I worded in a way that's difficult to understand. I'll also provide code here in this post.

I've got a macro built (albeit mostly from recording myself) to run a pivot table based on a report. Let's call this report, ReportABC_date. I created a macro for it and it runs great. I've got a near identical report that has the same column headers, etc. The only difference is workbook name/worksheet name. Let's call this, ReportDEF_date.

I thought I had augmented the recorded macro so that irrespective of the sheet name, it would run the macro. Instead I get a Run-time error '9' Subscript Out of Range

Here's the code:

Sub Pivot()

    Range("A1").Select
    Selection.End(xlDown).Select
    LastRow = ActiveCell.Row
    x = ActiveSheet.Name & "!R1C1:R" & LastRow & "C9"
    
    Sheets.Add.Name = "Pivot"
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "ReportABC_date!R1C1:R47C9", Version:=7).CreatePivotTable _
        TableDestination:="Pivot!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=7
    Sheets("Pivot").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1")
        .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("PivotTable1").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Administrator Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Participant Name"), "Count of Participant Name", _
        xlCount
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Available Balance"), "Sum of Available Balance", _
        xlSum

End Sub

Thanks!