I have the following macto to allow user to select a file in C:\Sales Data and to copy the data
I would like the code amended so as to use a wildard to narrow down the files when selecting the file to open. I need to select a files containg Sales-lite.xlsx
Youur assistance is most appreciated
I would like the code amended so as to use a wildard to narrow down the files when selecting the file to open. I need to select a files containg Sales-lite.xlsx
Code:
sub Open_Workbook()
ChDir ("c:\sales")
Sheets("Sales Data").Select
With Range
UsedRange.UnMerge
End With
Dim nb As Workbook, ts As Worksheet, A As Variant
Dim rngDestination As Range
Dim LR As Long
Dim wkbDest As Workbook
Set wkbDest = ThisWorkbook
LR = Cells(Rows.Count, "A").End(xlUp).Row
Set ts = ActiveSheet
With wkbDest
.Sheets("Sales Data").UsedRange.Cells.ClearContents
End With
On Error Resume Next
Set rngDestination = ts.[A2]
On Error GoTo 0
If rngDestination Is Nothing Then Exit Sub
A = Application.GetOpenFilename(A, Password:="XC98715")
If A = False Or IsEmpty(A) Then Exit Sub
Application.ScreenUpdating = False
Set nb = Workbooks.Open(Password:="XC98715")
ThisWorkbook.Activate
nb.Sheets("Sales").UsedRange.Cells.Copy
rngDestination.PasteSpecial Paste:=xlPasteValues
rngDestination.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
nb.Close savechanges:=False
With Application
.CutCopyMode = False
End With
ChDir ("c:\My Documents")
End Sub
Youur assistance is most appreciated