Hello. So I have this VBA code to identify two open workbooks and match data across them. One is Routing. One is Pickorder. This one is a unique name daily but always has PickOrder in it along with the current date. When this code is run a third workbook is open (not part of code). However when this is open AFTER the Pickorder one the code does not work and gives me errror code 9, "Subscript out of range." It works without that other workbook open or if the Pickorder is open after that other workbook. Also that one has a similar name to the Pickorder one but does not contain "Pickorder." Super weird issue and I am struggling to identify why. Any help would be appreciated. Thank you to anyone willing to help. Here is the line that is highlighted for the error and the rest of the code.
VBA Code:
Set PO = dwp.Sheets(Worksheets(i).Name]
Sub MatchCorrectTimes()
'CLICK HERE AND PRESS F5 TO START SCRIPT
For Each w In Workbooks
If UCase(w.Name) Like UCase("*Pick*order*") Then
Windows(w.Name).Activate
Exit For
End If
Next w
Dim dwp As Workbook 'DISPATCH
Dim crtx As Workbook 'CORTEX
Dim rngCTX As Range 'CORTEX RANGE
Dim sq As Range 'SPARE RANGE
Dim PO As Worksheet 'PICKORDER SHEET
Dim i As Long 'ITERATION
'FIND PICKORDER WORKBOOK & SHEET
For i = 1 To Workbooks.Count
If InStr(1, "PickOrder abc", "Pickorder", vbTextCompare) Then
Set dwp = Workbooks(i)
End If
Next i
For i = 1 To dwp.Worksheets.Count
If InStr(1, "PickOrder abc", "Pickorder", vbTextCompare) Then
Set PO = dwp.Sheets(Worksheets(i).Name)
End If
Next i
Last edited:
