Macro to Open File using Wildcard

[howard]

I have the following code to open a file and to copy and paste the data

I need the code amended so as to use a wildcard to select a file which starts with eMrb and ends with .xlsx for eg eMrb_listEnquiry.xlsx

Your assistance in this regard is most appreciated

Sub Open_Workbook()

ChDir ("C:\downloads")

Dim nb As Workbook, ts As Worksheet, A As Variant

Dim rngDestination As Range

Sheets("Imported Data").Select

With Range("a1:V2000")

.ClearContents

End With


Set ts = Sheets("Imported Data")



On Error Resume Next

Set rngDestination = ts.[a1]

On Error GoTo 0

If rngDestination Is Nothing Then Exit Sub

ChDir ("C:\extract")

MsgBox "Select Enquiry File"



A = Application.GetOpenFilename

If A = False Or IsEmpty(A) Then Exit Sub

Application.ScreenUpdating = False



Set nb = Workbooks.Open(Filename:=A, local:=True)

ThisWorkbook.Activate



nb.Sheets(1).Range("A1:V2000").Copy

rngDestination.PasteSpecial Paste:=xlPasteValues

rngDestination.PasteSpecial Paste:=xlPasteFormats

Application.CutCopyMode = False

nb.Close savechanges:=False

With Sheets("Imported Data")

.Range("A1:A4").EntireRow.Delete

End With

ChDir "C:\My documents"

End Sub

 

[John_w]

Try replacing:

A = Application.GetOpenFilename

If A = False Or IsEmpty(A) Then Exit Sub

with:

    A = Dir("C:\extract\eMrb*.xlsx")
    If A = vbNullString Then Exit Sub
    A = "C:\extract\" & A

 

[howard]

Thanks for the Help-This works perfectly