引用命名范围时出现Global 'Range of Object'错误求助

核心错误原因 #

1. 未指定Range的父工作表：所有Range()调用必须明确指定所属工作表，否则Excel会默认使用当前激活表，导致引用错位。
2. Range语法逻辑错误：行/列范围的拼接、引用方式不符合VBA规范，比如Rows(Range(4))这种写法完全错误。
3. 工作表数组引用错误：直接用数组中的字符串作为工作表对象，需先通过Worksheets()方法获取实际工作表对象。
4. With语句结构不完整：格式设置的With块嵌套错误，导致属性无法正确应用。

修正后的完整代码 #

Dim wbSource As Workbook
Dim wbTarget As Workbook
Dim shSource As Worksheet
Dim shTarget As Worksheet
Dim shHelper As Worksheet
Dim Rnga As Range ' 对应SKE的数值单元格
Dim Rngb As Range ' 对应Daily的数值单元格
Dim Rngc As Range
Dim Rngd As Range
Dim shStations As Variant
Dim i As Integer
Dim dailyRows As Integer
Dim skeRowOffset As Integer

' 初始化工作簿
Set wbSource = Workbooks.Open(Filename:="C:\Schedule.XLSX", ReadOnly:=True)
Set wbTarget = ThisWorkbook

' 初始化工作表
Set shSource = wbSource.Worksheets("Schedule")
Set shTarget = wbTarget.Worksheets("Master")
Set shHelper = wbTarget.Worksheets("Start")

' 绑定辅助单元格（直接用Range对象替代命名范围，更可靠）
Set Rnga = shHelper.Range("A1")
Set Rngb = shHelper.Range("B1")
Set Rngc = shHelper.Range("C1")

' 待隐藏的列
Set Rngd = shTarget.Range("A:B, F:F, I:I, K:L, N:AD")

' 提取关键数值，避免重复引用Range
dailyRows = Rngb.Value
skeRowOffset = Rnga.Value

Application.ScreenUpdating = False

' 清空Master表指定行（修正Range引用）
shTarget.Rows("4:" & dailyRows).Clear
shHelper.Calculate

' 初始化工作表数组（去掉名称末尾空格，避免匹配失败）
shStations = Array("Denest", "Decontent", "Columns", "Unishell", "5060", "EOL")

' 循环复制到各子工作表
For i = 0 To UBound(shStations)
    ' 复制源表指定行范围
    shSource.Rows(skeRowOffset + 2 - i).Resize(dailyRows).Copy
    ' 获取目标工作表对象
    With wbTarget.Worksheets(shStations(i))
        ' 粘贴到指定位置
        .Rows(dailyRows * i + 3 + i).PasteSpecial Paste:=xlPasteAllUsingSourceTheme
        ' 设置字体格式（修正With块嵌套）
        With .Rows("2:" & dailyRows * 8).Font
            .Name = "Calibri"
            .Size = 22
        End With
    End With
Next i
Application.CutCopyMode = False

' 循环复制到Master表
For i = 0 To UBound(shStations)
    shSource.Rows(skeRowOffset + 2 - i).Resize(dailyRows).Copy
    shTarget.Rows(dailyRows * i + 3 + i).PasteSpecial Paste:=xlPasteAllUsingSourceTheme
Next i

' 添加标题和日期
For i = 0 To UBound(shStations)
    shTarget.Cells(dailyRows * i + 2 + i, 4).Value = shStations(i) & Date
Next i

' 设置Master表字体格式
With shTarget.Rows("3:" & dailyRows * 8).Font
    .Name = "Calibri"
    '.Size = 22
End With
Application.CutCopyMode = False

' 自动调整列宽和行高
With shTarget.Rows("2:" & dailyRows * 8)
    .Columns.AutoFit
    .RowHeight = 35
End With
shTarget.Columns(4).ColumnWidth = 25

' 隐藏指定列
Rngd.EntireColumn.Hidden = True

Application.ScreenUpdating = True

关键修正点说明 #

• 直接使用Rnga.Value和Rngb.Value获取数值，替代易出错的命名范围引用。
• 所有Range/Rows调用都明确指定父工作表，比如shTarget.Rows(...)。
• 用Resize(dailyRows)替代错误的Rows("1:" & dailyRows)写法，更简洁规范。
• 修正工作表数组的名称（去掉末尾空格），并通过wbTarget.Worksheets(shStations(i))获取正确的工作表对象。
• 修复With语句的嵌套结构，确保格式属性正确应用到目标范围。

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