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

"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.