如何动态地获取不同的sourcedata范围？

Question

因此，我有以下代码使用记录宏，并将其更改为更具动态性。它在新表中创建一个枢轴表。我现在遇到的问题是，当我录制宏时，它具有固定范围，并希望做到这一点，因此每当运行宏时，它都会输入不同的范围，因为行的数量总是会更改。 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

结束子