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
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
Code:
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