Macro to Select File when opening

[howard]

I have the following macro that opens a file directory C:\extract and allows the user to select a file within this director and to copy and paste the data

The macro works perfectly, but I need it amended to allow the user to select a .xlsx file containing the name "Statement" eg XTRM Ltd Statement.xlsx


Your assistance in this regard is most appreciated

 Sub Open_Workbook()

Dim LR As Long

 '
      
        Application.ScreenUpdating = False
     Application.EnableEvents = False
     Application.Calculation = xlCalculationManual
    Dim nb As Workbook, ts As Worksheet, A As Variant
    Dim rngDestination As Range
  
    
    Set ts = ActiveSheet
    
    On Error Resume Next
    Set rngDestination = ts.[A1]
    On Error GoTo 0
    If rngDestination Is Nothing Then Exit Sub
    ChDir ("C:\extract")
MsgBox "Select Statement file applicable for this Branch"
    A = Application.GetOpenFilename
    If A = False Or IsEmpty(A) Then Exit Sub
    
    Application.ScreenUpdating = False
    
    Set nb = Workbooks.Open(A)
    ThisWorkbook.Activate
  
    
    
   nb.Sheets(1).Range("A1:Z2000").Copy
  
       rngDestination.PasteSpecial Paste:=xlPasteValues
    rngDestination.PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
       
    
    nb.Close savechanges:=False

    
         Application.ScreenUpdating = True
         
     Application.EnableEvents = True
     Application.Calculation = xlCalculationAutomatic
End Sub

 

[bobsan42]

the GetOpenFilename method can accept filters, but unfortunately the dialog box filters are limited to file extensions.
You can try this approach:

with Application.FileDialog(msoFileDialogFilePicker)
.InitialFileName ="*Statement*.xlsx"
If .Show = 0 Then Exit Function
A = .SelectedItems(1)
end with

 

[howard]

Thanks for the code.

Where do I include this with my code ?

 

[bobsan42]

Replace this line:

A = Application.GetOpenFilename

And replace exit function with exit sub.

 

[howard]

Many Thanks for the help. Code works perfectly