运行共享Cherwell_Daily宏触发Run Time Error 5：无效过程调用或参数

API 探路者

2026-6-18

针对Cherwell_Daily宏的命名/路径问题修改方案

以下是你提供的宏代码中，因原录制者的命名规则、文件路径导致报错的关键修改点：

1. 数据源工作表名称匹配

宏中创建数据透视表时指定了固定数据源工作表：

"Export Data!R1C1:R1048576C10"

如果你的原始数据所在工作表名称不是Export Data，请将此处替换为你的实际工作表名称（比如"原始数据"）。

2. 硬编码的外部文件引用

宏中直接调用了原录制者的本地文件：

Windows("Weekly 081522.xlsx").Activate
Windows("Res 081522.xlsx").Activate

这两个是原录制者的特定文件名，你需要替换为自己实际使用的文件名称；如果不需要操作外部文件，可直接删除这几段激活+粘贴的代码块。

3. 固定数据范围的硬编码

宏中使用了固定的筛选范围：

ActiveSheet.Range("$A$1:$J$93").AutoFilter Field:=3, Criteria1:="Resolved"
Rows("1:66").Select

这是原录制时的数据行数，你的数据行数肯定不同，建议改成动态获取数据区域：

' 替换固定范围为动态获取
Dim dataRange As Range
Set dataRange = ActiveSheet.UsedRange
dataRange.AutoFilter Field:=3, Criteria1:="Resolved"
' 复制筛选后的可见行
dataRange.SpecialCells(xlCellTypeVisible).Copy

4. 无限递归的宏调用

宏中重复调用自身：

Application.Run "PERSONAL.XLSB!Cherwell_Daily"

这会导致宏无限循环运行，直接删除这两行代码即可。

5. 工作表名称冲突风险

宏中创建新表后固定命名为"Pivot"：

Sheets("Sheet1").Name = "Pivot"

如果你的工作簿中已经存在名为Pivot的工作表，会触发报错。可以修改为动态命名（比如"每日透视_" & Format(Date, "YYYYMMDD")），或者先检查是否存在该表再命名。

完整宏代码（标注修改建议）

Sub Cherwell_Daily()
'
' Cherwell_Daily Macro
' Cherwell Daily Report
'
' Keyboard Shortcut: Ctrl+Shift+C
'
    Rows("1:1").Select
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("A:A").EntireColumn.AutoFit
    Cells.Select
    Selection.ColumnWidth = 8.71
    Cells.EntireColumn.AutoFit
    Sheets.Add
    ' 修改点1：替换"Export Data"为你的数据源工作表名称
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Export Data!R1C1:R1048576C10", Version:=7).CreatePivotTable _
        TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=7
    Sheets("Sheet1").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("Owned By")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Incident ID"), "Count of Incident ID", xlCount
    With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "SLA Resolve By Deadline")
        .Orientation = xlPageField
        .Position = 1
    End With
    Sheets("Sheet1").Select
    ' 修改点5：可改为动态命名避免冲突
    Sheets("Sheet1").Name = "Pivot"
    Range("B15").Select
    ActiveWorkbook.Save
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet2").Select
    Rows("1:1").Select
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Cells.Select
    Cells.EntireColumn.AutoFit
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Selection.AutoFilter
    ' 修改点3：替换固定范围为动态获取
    ActiveSheet.Range("$A$1:$J$93").AutoFilter Field:=3, Criteria1:="Resolved"
    Rows("1:66").Select
    Selection.Copy
    ' 修改点2：替换为你的实际文件名，或删除这段外部文件操作代码
    Windows("Weekly 081522.xlsx").Activate
    ActiveSheet.Paste
    Cells.Select
    Cells.EntireColumn.AutoFit
    Application.CutCopyMode = False
    ' 修改点4：删除该行，避免无限递归
    Application.Run "PERSONAL.XLSB!Cherwell_Daily"
    ' 修改点2：替换为你的实际文件名，或删除
    Windows("Res 081522.xlsx").Activate
    Range("D103").Select
    ' 修改点2：替换为你的实际文件名，或删除
    Windows("Weekly 081522.xlsx").Activate
    Application.Left = -1013.75
    Application.Top = 80.5
    Sheets("Export Data").Select
    ActiveWorkbook.Save
    ActiveWindow.Close
    Application.Left = -1148
    Application.Top = 97
    Rows("2:2").Select
    ' 修改点4：删除该行，避免无限递归
    Application.Run "PERSONAL.XLSB!Cherwell_Daily"
    Sheets("Sheet4").Select
    ActiveWindow.SelectedSheets.Delete
    Rows("2:2").Select
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    Rows("4:4").Select
    With Selection.Font
        .Name = "Calibri"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .Color = -16776961
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Rows("6:71").Select
    With Selection.Font
        .Name = "Calibri"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .Color = -16776961
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    ActiveWindow.SmallScroll Down:=60
    Rows("73:82").Select
    With Selection.Font
        .Name = "Calibri"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .Color = -16776961
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Rows("84:88").Select
    With Selection.Font
        .Name = "Calibri"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .Color = -16776961
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range("D92").Select
    ActiveWindow.SmallScroll Down:=-102
    Sheets("Pivot").Select
End Sub

内容的提问来源于stack exchange，提问作者user27088330

运行共享Cherwell_Daily宏触发Run Time Error 5：无效过程调用或参数

1. 数据源工作表名称匹配 #

2. 硬编码的外部文件引用 #

3. 固定数据范围的硬编码 #

4. 无限递归的宏调用 #

5. 工作表名称冲突风险 #