VBA line of code no longer recognizing open workbook after name change

[Indominus]

Hello. I have this code that matches up data from one workbook to another and pastes in information from one to another. However, one of the workbooks now has a different name format and it will no longer recognize it. Gives me the object variable error. The old name was in the format of DspPickOrder-2020-current date. The new one is named current date-DSP-PickOrder. Here is the code. The line it gives me the error is If InStr(Workbooks(i).Name, "Pickorder"). The code will no longer recognize the "Pickorder name in the new one. Here is the main part of the code. Thanks to anyone willing to help.

Sub Match()

    
    
    
    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 dsp 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(Workbooks(i).Name, "Pickorder") Then
            Set dsp = Workbooks(i)
        End If
    Next i

    For i = 1 To dsp.Worksheets.Count
        If InStr(Worksheets(i).Name, "Pickorder") Then
            Set PO = dsp.Sheets(Worksheets(i).Name)
        End If
    Next i

 

[Logit]

The best thing is to insure your file names have the same format. Why are they so different ?

 

[Indominus]

The best thing is to insure your file names have the same format. Why are they so different ?

I get sent the Pickorder file. It is part of a big macro. Do not want to add a tedious manual step of resaving it.

 

[Peter_SSs]

The line it gives me the error is If InStr(Workbooks(i).Name, "Pickorder").

Try

If InStr(Workbooks(i).Name, "Pickorder", vbTextCompare) Then

 

[Indominus]

Try

If InStr(Workbooks(i).Name, "Pickorder", vbTextCompare) Then

Hello. Getting error 13 type mismatch

 

[Peter_SSs]

Hello. Getting error 13 type mismatch

Sorry, missed a parameter.

If InStr(1, Workbooks(i).Name, "Pickorder", vbTextCompare) Then

 

[Indominus]

Sorry, missed a parameter.

If InStr(1, Workbooks(i).Name, "Pickorder", vbTextCompare) Then

Thank you so much it works perfectly. Very much appreciated. I’m trying to learn more about VBA. What does this code do that works that my other one did not?

 

[Peter_SSs]

Thank you so much it works perfectly. Very much appreciated.

Good news .. and you are welcome.

What does this code do that works that my other one did not?

It comes down to that final argument: vbTextCompare
That argument is optional and you omitted it. The default value for that is taken from your Option Compare setting and if you have not specifically set it the default for Option Compare is a Binary comparison.
That effectively means that your InStr function is looking for exactly "Pickorder" and would not count "PickOrder", "pickorder", "PICKORDER" etc as a match.
vbTextCompare makes the search case-insensitive. However, when that final argument is used, the first argument (starting position) cannot be omitted which was my mistake in post 4.