VBA MACRO TO SET WORKBOOK NAME WITH PARTIAL NAME

[mayoung]

I am trying to set workbook 1 to pick up a partial workbook name of Current Hilliard WIP. with the code I have I am getting a Complie error: Type Mismatch and it highlihts the ampersand in the following
part of the code: Set wb1 = wbName1 & "*" How can I adjust this?

        Dim wb1    As Workbook, wb2 As Workbook, r As Long, K As String
        Dim wbName1 As String
        '~~> "Current Hilliard WIP"
        wbName1 = "Current Hilliard WIP"
        Set wb1 = wbName1 & "*": Set wb2 = Workbooks("PartsFile.xml")
        With CreateObject("Scripting.Dictionary")
            r = 2: Do Until wb2.Sheets("PARTS").Range("D" & r) = ""
            K = Trim(wb2.Sheets("PARTS").Range("D" & r)): .Item(K) = r
            r = r + 1: Loop
            r = 2: Do Until wb1.Sheets("WIP-MAIN").Range("D" & r) = ""
            K = Trim(wb1.Sheets("WIP-MAIN").Range("D" & r))
            If .Exists(K) Then
                '           wb1.Sheets("WIP-MAIN").Range("D" & r).Interior.ColorIndex = 33
                wb1.Sheets("WIP-MAIN").Range("C" & r).Interior.ColorIndex = 33
                wb1.Sheets("WIP-MAIN").Range("E" & r).Interior.ColorIndex = 33
            Else
                '           wb1.Sheets("WIP-MAIN").Range("D" & r).Interior.ColorIndex = 0
                wb1.Sheets("WIP-MAIN").Range("C" & r).Interior.ColorIndex = 0
                wb1.Sheets("WIP-MAIN").Range("E" & r).Interior.ColorIndex = 0
            End If
            r = r + 1: Loop
        End With

 

[offthelip]

this code should do it for you:

Dim wbName1 As String
Dim wbook As Workbook
Dim tt As String
Dim wb1    As Workbook
wbName1 = "Current Hilliard WIP"

For Each wbook In Workbooks
  tt = wbook.Name
  If InStr(tt, wbName1) Then
   Set wb1 = wbook
   Exit For
  End If
Next wbook

 

[mayoung]

this code should do it for you:

Dim wbName1 As String
Dim wbook As Workbook
Dim tt As String
Dim wb1    As Workbook
wbName1 = "Current Hilliard WIP"

For Each wbook In Workbooks
  tt = wbook.Name
  If InStr(tt, wbName1) Then
   Set wb1 = wbook
   Exit For
  End If
Next wbook

How would I incorporate this into my code?

 

[offthelip]

change these two lines:

        wbName1 = "Current Hilliard WIP"
        Set wb1 = wbName1 & "*": Set wb2 = Workbooks("PartsFile.xml")

to all of the code I posted then add this line

 Set wb2 = Workbooks("PartsFile.xml")

 

[mayoung]

change these two lines:

        wbName1 = "Current Hilliard WIP"
        Set wb1 = wbName1 & "*": Set wb2 = Workbooks("PartsFile.xml")

to all of the code I posted then add this line

 Set wb2 = Workbooks("PartsFile.xml")

I was able to get this to get your code to work. Can you please help me with one other issue.
I am trying to use the following code but it is giving me an error because of my wild card I am using in the Current Hilliard WIP workbook name. How can this be modified to recognize only the first part of the workbook name? I am finding out that working with partial names is a whole new learning experience.. Thank You

Sheets("PARTS").Copy After:=Workbooks("Current Hilliard WIP*.xlsm").Sheets(2)

 

[offthelip]

Having very carefully set wb1 to the workbook with "current Hilliard etc" in the name why not use wb1 instead e.g.

Sheets("PARTS").Copy After:=wb1.Sheets(2)

 

[mayoung]

Having very carefully set wb1 to the workbook with "current Hilliard etc" in the name why not use wb1 instead e.g.

Sheets("PARTS").Copy After:=wb1.Sheets(2)

Thank You...

 

[smozgur]

I was able to get this to get your code to work.

@mayoung - The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.