在 Excel VBA 代码上，一个宏在单独运行时工作，但在组中运行时进行调试

Question

我的程序通过调用许多宏来工作，如下所示：

Sub RunAll()
Call BenNaimGal
Call NunnariMaureen
Call FarberovDimitry
End Sub

我的程序在 macro3 调用 FarberovDimitry 时中断，它本质上是 macro1 和 macro2 的精确副本，工作正常。当 macro3 自行运行时，它可以完美运行，但是当我尝试运行整个程序时，缺少一些行，

Rng4.Copy Destination:=WS.Range("A5:AJ50")

A5：AJ50 为空白

以下是这 3 个宏的代码

Sub BenNaimGal()
Dim WS As Worksheet
Dim Rng As Range
Set Rng = Range("A1:AJ14")
Application.Workbooks.Add
Set WS = Application.ActiveSheet
Rng.Copy Destination:=WS.Range("A1:AJ20")
Rows(3).RowHeight = 36
Rows(4).RowHeight = 64.5
Rows("6:50").RowHeight = 15
Columns(11).ColumnWidth = 11.57
Columns(12).ColumnWidth = 30.43
Columns(14).ColumnWidth = 15.57
Columns(22).ColumnWidth = 14
Columns(24).ColumnWidth = 13.71
Columns(25).ColumnWidth = 13.71
Set column1 = Columns("A:J")
column1.Hidden = True
Set column2 = Columns("R:T")
column2.Hidden = True
Set column3 = Columns("M:M")
column3.Hidden = True
Set column4 = Columns("U:U")
column4.Hidden = True
Set column5 = Columns("W:W")
column5.Hidden = True
Set column6 = Columns("Z:AJ")
column6.Hidden = True
[N1].Value = "Gal"
[O1].Value = "Ben-Naim"
[N2].Value = "CA . PBI. 3410"
WS.SaveAs "L:17_Year_End_KPIs2018Master KIP 2018 v1 - DistributionBen- 
Naim, Gal"
End Sub
Sub NunnariMaureen()
Dim WS As Worksheet
Dim Rng As Range
Set Rng1 = Range("A1:AJ4")
Set Rng2 = Range("A15:AJ25")
Application.Workbooks.Add
Set WS = Application.ActiveSheet
Rng1.Copy Destination:=WS.Range("A1:AJ4")
Rng2.Copy Destination:=WS.Range("A5:AJ50")
Rows(3).RowHeight = 36
Rows(4).RowHeight = 64.5
Rows("6:50").RowHeight = 15
Columns(11).ColumnWidth = 11.57
Columns(12).ColumnWidth = 30.43
Columns(14).ColumnWidth = 15.57
Columns(22).ColumnWidth = 14
Columns(24).ColumnWidth = 13.71
Columns(25).ColumnWidth = 13.71

Set column1 = Columns("A:J")
column1.Hidden = True
Set column2 = Columns("R:T")
column2.Hidden = True
Set column3 = Columns("M:M")
column3.Hidden = True
Set column4 = Columns("U:U")
column4.Hidden = True
Set column5 = Columns("W:W")
column5.Hidden = True
Set column6 = Columns("Z:AJ")
column6.Hidden = True
[N1].Value = "Nunnari"
[O1].Value = "Maureen"
[N2].Value = "CA . C&I. 3420"
WS.SaveAs "L:17_Year_End_KPIs2018Master KIP 2018 v1 - 
DistributionNunnari, Maureen"
End Sub
Sub FarberovDimitry()
Dim WS As Worksheet
Dim Rng As Range
Set Rng3 = Range("A1:AJ4")
Set Rng4 = Range("A26:AJ33")
Application.Workbooks.Add
Set WS = Application.ActiveSheet
Rng3.Copy Destination:=WS.Range("A1:AJ4")
Rng4.Copy Destination:=WS.Range("A5:AJ50")
Rows(3).RowHeight = 36
Rows(4).RowHeight = 64.5
Rows("6:50").RowHeight = 15
Columns(11).ColumnWidth = 11.57
Columns(12).ColumnWidth = 30.43
Columns(14).ColumnWidth = 15.57
Columns(22).ColumnWidth = 14
Columns(24).ColumnWidth = 13.71
Columns(25).ColumnWidth = 13.71

Set column1 = Columns("A:J")
column1.Hidden = True
Set column2 = Columns("R:T")
column2.Hidden = True
Set column3 = Columns("M:M")
column3.Hidden = True
Set column4 = Columns("U:U")
column4.Hidden = True
Set column5 = Columns("W:W")
column5.Hidden = True
Set column6 = Columns("Z:AJ")
column6.Hidden = True
[N1].Value = "Dimitry"
[O1].Value = "Farberov"
[N2].Value = "CA . PBI. 3409"
WS.SaveAs "L:17_Year_End_KPIs2018Master KIP 2018 v1 - 
DistributionFarberov, Dimitry"
End Sub

Answer 1

最好指定一张 rng 表。

Dim WS As Worksheet
Dim Rng As Range
Dim myWs As Worksheet
Set myWs = ThisWorkbook.Sheets(1)
Set Rng = myWs.Range("A1:AJ14")