SOLVED

wb.Name Like VBA Not Working, Most of the Time

Contributor
Hello,
 

This code below sometimes works sometimes doesn't. I don't get why. The file is in CSV format. Also, adding Debug.Print wb.Name line produces a result of the csv file not being displayed in immediate window. I'm guessing it's because it's running in a different instance. How can I fix this?

 

 

 

Sub CopyRAWMMP()
Dim Ct As Long
For Each wb In Application.Workbooks
    If wb.Name Like "data-*" Then
        Ct = Ct + 1
        wb.Activate
        Exit For
    End If
Next wb
    If Ct = 0 Then MsgBox "MSG"
    
    Range("A2:N2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Copy
    Workbooks("Real Time.xlsm").Activate
    Worksheets("Raw MMP").Activate
    Range("RawMMP[Date]").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

 

 

 

I'd really appreciate your assistance.

 

 

Thanks.

1 Reply
best response confirmed by kheldar (Contributor)
Solution

@kheldar 

 

On the safer side, you may change the logical test as below to remove any leading space in the file name and ignore the letter case and see if this works for you...

 

If Trim(LCase(wb.Name)) Like "data-*" Then