需要帮助更正嵌套在循环中的子例程中的 'with' 语句

Question

下面是一个宏，我一直在工作，更新一组值在所有"数字"表(即有数字名称的表)使用数据从主表名为"BW TB"。

出于某种原因，子例程"ClearContents"清除了所有数字表中的数据，但也在母表中(因此没有使用其他两个子例程复制粘贴)，我不知道为什么!完整的代码如下;请查看:

Option Explicit
Dim BW As String, FirstRow As Integer, LastRow As Integer, ColNo As Integer, i As Integer
Sub Refresh_Data()
    Application.CutCopyMode = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    'Defines the range of rows and columns in the refreshed BW query
    BW = "BW TB"
    Worksheets(BW).Activate
    Range("A1").Activate
    Dim MyCell As Range
    Set MyCell = Cells.Find(What:="Overall Result", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    True, SearchFormat:=False)
    FirstRow = MyCell.End(xlUp).Row + 1
    LastRow = MyCell.Row - 1
    ColNo = MyCell.Column
    'loop to update numeric sheets
    For i = 1 To Sheets.Count
    If IsNumeric(Sheets(i).Name) Then
        Call Clearcontents
        Call PasteGLCodes
        Call PasteTBValues
    End If
    Next
    Call CheckTotals
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Private Sub Clearcontents()
'clears the contents of the sheet of Row 6 to 1000 for every column containing data in Row 6
Dim ColRange As Integer
With Worksheets(i)
    ColRange = .Cells(6, .Columns.Count).End(xlToLeft).Column
    .Range("A6", .Cells(1000, ColRange)).Clearcontents
End With    
End Sub
Private Sub PasteGLCodes()
'Pastes the range of GL codes from ColumnA
With Worksheets(BW)
    Range(.Cells(FirstRow, ColNo), .Cells(LastRow, ColNo)).Copy
End With
Worksheets(i).Range("A5").PasteSpecial xlPasteValues
End Sub
Private Sub PasteTBValues()
'Copies the formula from top row and drags to the last row
Range("B5:L5").Copy
Range("B5:L5", Range("B5:L5").Offset(LastRow - FirstRow, 0)).PasteSpecial xlPasteFormulas
'Recalculates the formulae
ActiveSheet.Calculate
'Pastes the values from the second row down to the last row
Range("B6:L6", Range("B6:L6").Offset(LastRow - FirstRow, 0)).Copy
Range("B6").PasteSpecial xlPasteValues
End Sub
Private Sub CheckTotals()
Application.Goto Worksheets("Control sheet").Range("AU114"), True
MsgBox "Update complete - check control totals"
End Sub

如果我替换ClearContents为:

Private Sub Clearcontents()
    Sheets(i).Activate
    Range("A6").EntireRow.Select
    Range(Selection, Selection.Offset(1000, 0)).Clearcontents
End Sub

它工作得很好，但它显然是一个不太干净的解决方案。

如往常一样，任何帮助都非常感谢!

Answer 1

尝试更改

.Range("A6", .Cells(1000, ColRange)).Clearcontents

.Range(.Range("A6"), .Cells(1000, ColRange)).Clearcontents

在你的Clearcontents子目录

EDIT我看到你的问题:Clearcontents和PasteGLCodes都没有激活第I个表，所以你对PasteTBValues的调用总是会在你开始运行时激活的表上运行("BW TB")。你需要改变最后一个子元素这样它就能作用于第i个表单

Answer 2

如果工作簿中有任何图表，那么您将引用不同的工作表，因为在Refresh_Data方法中使用的是Sheets，而在ClearContents方法中使用的是WorkSheets

工作表集合包含工作表和图表表。

工作表集合只包含工作表。

因此，在ClearContents方法中使用Sheets