How to get range of dynamic data for Pivot Table SourceData?

[XcelNoobster]

I have the following code to create a pivot table. The issue I am having is idk how to get dynamically get the SourceData. Below I provided the code. I know I have to change the fixed Sheet1!R1C1:R193C9.

    Worksheets("Sheet1").Activate
    Sheets.Add(After:=Sheets("Sheet1")).Name = "Result"
    
    Windows("User_Signoff_Duration_Report (version 1).xlsb").Activate
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R193C9", Version:=7).CreatePivotTable TableDestination:= _
        "Result!R3C1", TableName:="PivotTable2", DefaultVersion:=7
    Sheets("Result").Select
    Cells(3, 1).Select
    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
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Reviewer Job Function" _
        )
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Status")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Signoff Duration"), "Sum of Signoff Duration", _
        xlSum
    With ActiveSheet.PivotTables("PivotTable2").PivotFields( _
        "Sum of Signoff Duration")
        .Caption = "Average of Signoff Duration"
        .Function = xlAverage
        .NumberFormat = "0.00"
    End With
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Status").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Status").CurrentPage = _
        "CCB"
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.SetSourceData Source:=Range("Result!$A$3:$B$10")
    ActiveSheet.Shapes("Chart 1").IncrementLeft 63.5
    ActiveSheet.Shapes("Chart 1").IncrementTop -77
    ActiveSheet.Shapes("Chart 1").ScaleWidth 1.3020833333, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("Chart 1").ScaleHeight 1.1643518519, msoFalse, _
        msoScaleFromTopLeft
    ActiveChart.SetElement (msoElementDataLabelOutSideEnd)
    ActiveCell.Offset(0, 11).Range("A1").Select
    Windows("NewMacros.xlsm").Activate
End Sub

 

[Alex Blakenburg]

See if this works for you:
(replace code before Sheets("Result").Select, with the below)

    Worksheets("Sheet1").Activate
    Sheets.Add(After:=Sheets("Sheet1")).Name = "Result"
    
    Dim pvtSrc As Range
    Set pvtSrc = Worksheets("Sheet1").Range("A1").CurrentRegion
    
    Windows("User_Signoff_Duration_Report (version 1).xlsb").Activate
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=pvtSrc, Version:=7).CreatePivotTable _
        TableDestination:="Result!R3C1", TableName:="PivotTable2", DefaultVersion:=7