excel - 为什么在运行“For Loop”的 vba 代码以复制数据时出现“脚本超出范围”错误？

Question

Sub GenerateProductionReport()
    '*Let’s define the variables*    
    Dim GCell As Range
    Dim Txt$, MyPath$, MyWB$, MySheet$
    Dim myValue As Integer
    Dim Number As Integer
    Dim cmdprodhrsreport As Long
    Dim LastRow As Long
    Dim r As Long
    Dim LastRowRpt As Long
    
    '*Search what*
    MyPath = "\\Mypath\xxx\xxx\xxx\"
    
    '*The name of the workbook to be searched*
    MyWB = "Daily Data Tracker.xlsx"
    
    Workbooks.Open FileName:=MyPath & MyWB
    LastRow = Worksheets("Sheet1").Range("A" & Rows.count).End(xlUp).Row
    
    '*Use the current sheet to store the found data*
    For r = 2 To LastRow 'In ThisWorkbook.Worksheets
        If Worksheets("Sheet1").Range("D" & r).Value = Adminaccess.txtstartdate.Value Then'problem here for next r
             Worksheets("Sheet1").Range("D" & r & ":G" & r & ",K" & r & ":Q" & r).Copy

            Application.ScreenUpdating = False
        
            ThisWorkbook.Worksheets("Production").Activate
            '*Range("A2:K99999").ClearContents*
            MySheet = ActiveSheet.Name 
            '*Range("A2:K99999").ClearContents*
    
            LastRowRpt = Worksheets("Production").Range("A" & Rows.count).End(xlUp).Row
    
            Worksheets("Production").Range("A" & LastRowRpt + 1).Select
            ActiveSheet.Paste
        End If
    Next r
    
    'ThisWorkbook.Worksheets("Sheet1").SetFocus
     
    '*Close data workbook; don’t save it; turn screen updating back on*
    ActiveWorkbook.Close savechanges:=False
    Application.ScreenUpdating = True
    
    Exit Sub
End Sub

标签： excelvba

Answer 1

因为ThisWorkbook.Worksheets("Production").Activate在循环中，在下一次迭代Worksheets("Sheet1")将属于ThisWorkbook.

要成为/保持属于 的那个MyWB，就像我想的那样，您必须在代码的开头使用：

Dim sh1 as Worksheet

并且MyWB打开后Set sh1 = Worksheets("Sheet1")

其次是 LastRow = sh1.Range("A" & Rows.count).End(xlUp).Row。

然后，将有问题的行更改为

sh1.Range("D" & r & ":G" & r & ",K" & r & ":Q" & r).Copy