Excel VBA多工作表操作时_Worksheet对象Error 1004问题排查

问题根源 #

你遇到的1004错误，核心原因是未明确指定Cells所属的工作表。不用.Select时，Cells(X, "A")默认指向当前激活的工作表，而你试图用它定义另一工作表（比如Escopo）的Range范围，这就造成了跨工作表的无效引用，触发错误。

启用.Select后，虽然能临时让Cells指向目标工作表，但.Select操作会强制切换激活工作表，直接导致Application.ScreenUpdating = False失效——因为切换工作表的动作会强制刷新屏幕。

修正方案 #

给所有Cells对象前加上对应的工作表变量，明确指定单元格所属的工作表即可，完全不需要.Select或.Activate。

修正后的完整代码 #

Sub FormatWB()

Dim X As Long
Dim AA As Long
Dim Escopo As Worksheet
Dim Material As Worksheet
Dim PrecoMTL As Worksheet
Dim PrecoRev As Worksheet
Dim Orcamento As Worksheet

Set Escopo = ActiveWorkbook.Worksheets("Escopo")
Set Material = ActiveWorkbook.Worksheets("Material")
Set PrecoMTL = ActiveWorkbook.Worksheets("Preço Material")
Set PrecoRev = ActiveWorkbook.Worksheets("Preço Revestimento")
Set Orcamento = ActiveWorkbook.Worksheets("Orçamento Final")

Application.DisplayAlerts = False
Application.ScreenUpdating = False
 
    X = 4 '初始行号

        Do Until IsEmpty(Escopo.Cells(X, "B"))
        
            With Escopo.Range(Escopo.Cells(X, "A"), Escopo.Cells(X, "Y"))
                .UnMerge
                .Borders.LineStyle = xlContinuous
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .NumberFormat = "@" '转换为文本格式
                .Font.Bold = False
                .Font.Italic = False
                .Font.Underline = False
                .Font.Name = "Calibri"
                .Font.Size = 11
                .Interior.ColorIndex = 0
                .Font.Color = vbBlack
            End With                                                    

            With Material.Range(Material.Cells(X, "A"), Material.Cells(X, "N"))
                .UnMerge
                .Borders.LineStyle = xlContinuous
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .NumberFormat = "@" '转换为文本格式
                .Font.Bold = False
                .Font.Italic = False
                .Font.Underline = False
                .Font.Name = "Calibri"
                .Font.Size = 11
                .Interior.ColorIndex = 0
                .Font.Color = vbBlack
            End With

            With PrecoMTL.Range(PrecoMTL.Cells(X, "A"), PrecoMTL.Cells(X, "P"))
                .UnMerge
                .Borders.LineStyle = xlContinuous
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .NumberFormat = "@" '转换为文本格式
                .Font.Bold = False
                .Font.Italic = False
                .Font.Underline = False
                .Font.Name = "Calibri"
                .Font.Size = 11
                .Interior.ColorIndex = 0
                .Font.Color = vbBlack
            End With

            With PrecoRev.Range(PrecoRev.Cells(X, "A"), PrecoRev.Cells(X, "O"))
                .UnMerge
                .Borders.LineStyle = xlContinuous
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .NumberFormat = "@" '转换为文本格式
                .Font.Bold = False
                .Font.Italic = False
                .Font.Underline = False
                .Font.Name = "Calibri"
                .Font.Size = 11
                .Interior.ColorIndex = 0
                .Font.Color = vbBlack
            End With

            With Orcamento.Range(Orcamento.Cells(X, "A"), Orcamento.Cells(X, "Q"))
                .UnMerge
                .Borders.LineStyle = xlContinuous
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .NumberFormat = "@" '转换为文本格式
                .Font.Bold = False
                .Font.Italic = False
                .Font.Underline = False
                .Font.Name = "Calibri"
                .Font.Size = 11
                .Interior.ColorIndex = 0
                .Font.Color = vbBlack
            End With
            
    X = X + 1
        Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

额外优化建议 #

你可以把重复的格式代码封装成独立子过程，减少冗余：

Sub FormatRange(rng As Range)
    With rng
        .UnMerge
        .Borders.LineStyle = xlContinuous
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .NumberFormat = "@"
        .Font.Bold = False
        .Font.Italic = False
        .Font.Underline = False
        .Font.Name = "Calibri"
        .Font.Size = 11
        .Interior.ColorIndex = 0
        .Font.Color = vbBlack
    End With
End Sub

之后在主过程中调用：

FormatRange Escopo.Range(Escopo.Cells(X, "A"), Escopo.Cells(X, "Y"))
FormatRange Material.Range(Material.Cells(X, "A"), Material.Cells(X, "N"))
'... 其他工作表同理

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