Why am I getting a "Script out of range" error while running the vba code for "For Loop" to copy data?

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

Answer 1

Because of ThisWorkbook.Worksheets("Production").Activate in the loop, at next iteration Worksheets("Sheet1") will belong to ThisWorkbook.

To be/remain the one belonging to MyWB, like I suppose, you must use at the beginning of the code:

Dim sh1 as Worksheet

and after MyWB opening Set sh1 = Worksheets("Sheet1")

followed by LastRow = sh1.Range("A" & Rows.count).End(xlUp).Row.

Then, change the problematic line in

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

Answer 2

"Subcript out of range" (run-time error 9) is a common error that means you're trying to retrieve an item from an array or collection, using a subscript that doesn't exist in that array/collection.

Dim things(0 To 9) As Long
things(10) = 42 '<~ "subscript out of range"

The Excel object model raises that error when you try to retrieve a Worksheet from a Sheets collection by name, but the specified sheet doesn't exist in the supplied Sheets collection.

ActiveWorkbook.Worksheets("boom") '<~ if sheet is not in ActiveWorkbook sheets, error 9 is raised.

The trick is to always be explicit about what objects belong to what. For example:

Workbooks.Open FileName:=MyPath & MyWB
LastRow = Worksheets("Sheet1").Range("A" & Rows.count).End(xlUp).Row

Here we're explicit about what Worksheet this Range call should work with (but Rows is implicitly qualified with the ActiveSheet), but the Worksheets collection is implicitly referring to whatever the ActiveWorkbook is. That works, because by then we've just opened a workbook so that workbook must be active!

Workbooks.Open is a function that returns a reference to the opened Workbook object. Consider capturing that reference and removing the implicit reliance on the function's side-effects:

Dim book As Workbook
Set book = Application.Workbooks.Open(MyPath & MyWB)

So here:

'*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

If the intent is to iterate rows in ThisWorkbook, that's not what's happening here, because If Worksheets("Sheet1")... isn't explicit about what workbook it's working against, so if the ActiveWorkbook isn't ThisWorkbook and there's no worksheet named "Sheet1" in there, that throws error 9.

Unqualified, Worksheets refers to ActiveWorkbook.Worksheets unless the code is written in the ThisWorkbook module (see this answer for why).

So the simplest solution is to just qualify things:

'*Use the current sheet to store the found data*
For r = 2 To LastRow 'In ThisWorkbook.Worksheets
    If ThisWorkbook.Worksheets("Sheet1").Range("D" & r).Value = Adminaccess.txtstartdate.Value Then'problem here for next r
         ThisWorkbook.Worksheets("Sheet1").Range("D" & r & ":G" & r & ",K" & r & ":Q" & r).Copy

Now the code says what it does, and does what it says - without implicitly relying on side-effects and global state.

If "Sheet1" exists in ThisWorkbook at compile-time (ThisWorkbook is always the workbook that contains your VBA project), then you can use the sheet's CodeName to define a compile-time identifier that you can use as-is to refer to that specific sheet anywhere in the code - for example if you found the "Sheet1" in the VBE's Project Explorer and then explored its properties (F4), you could change its (Name) property from Sheet1 to something meaningful.

Same with the "Production" sheet, could be just ProductionSheet, and then there's no need to dereference it from any Sheets collection anymore, it's just there waiting to be used.

Avoid implicit ActiveWorkbook references - the real solution is never to Activate anything; the solution is to stop implicitly qualifying member calls (i.e. Worksheet -> someWorkbook.Worksheets, Range -> someSheet.Range) that belong to an object.