VBA code to identify one open workbook only working when certain workbook is open in a specific order?

Indominus

Board Regular
Joined
Jul 11, 2020
Messages
66
Office Version
  1. 2016
Platform
  1. Windows
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:

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
What is the name of the workbook that's causing the problem?
 

Indominus

Board Regular
Joined
Jul 11, 2020
Messages
66
Office Version
  1. 2016
Platform
  1. Windows
What is the name of the workbook that's causing the problem?

Looks like 2020-09-12-DU2-CYCLE_2-DSP-DayOfOpsPlan.xlsx. The date is always the current date. Rest stays the same. The Pickorder one is the same but instead of “DayOfOpsPlan” it is “PickOrder.”
 
Last edited:

Indominus

Board Regular
Joined
Jul 11, 2020
Messages
66
Office Version
  1. 2016
Platform
  1. Windows
Looks like 2020-09-12-DU2-CYCLE_2-DSP-DayOfOpsPlan.xlsx. The date is always the current date. Rest stays the same. The Pickorder one is the same but instead of “DayOfOpsPlan” it is “PickOrder.”
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
What about the 3rd workbook?
 

Indominus

Board Regular
Joined
Jul 11, 2020
Messages
66
Office Version
  1. 2016
Platform
  1. Windows
What about the 3rd workbook?
The third one that is not part of the code is 2020-09-12-DU2-CYCLE_2-DSP-DayOfOpsPlan.xlsx . The two other open ones that are part of the code is Routing.xlsm and 2020-09-12-DU2-CYCLE_2-DSP-PickOrder.xlsx
 

Watch MrExcel Video

Forum statistics

Threads
1,109,341
Messages
5,528,146
Members
409,803
Latest member
Jeff abby

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top