I have a macro below to open a workbook and select directory C:\extract and to copy the data
when running the directory "C:\extract" directory is not selected
Kindly check & amend my code
when running the directory "C:\extract" directory is not selected
Kindly check & amend my code
Code:
Sub Open_Workbook()
ChDir "C:\extract\"
Dim nb As Workbook, ts As Worksheet, A As Variant
Dim rngDestination As Range
Sheets("Imported Data").Select
With Range("a1:AD2000")
.ClearContents
End With
Set ts = ActiveSheet
On Error Resume Next
Set rngDestination = ts.[A1]
On Error GoTo 0
If rngDestination Is Nothing Then Exit Sub
MsgBox ("select Sales Data Report Inquiry.xlsx File")
ChDir "C:\extract\"
With Application.FileDialog(msoFileDialogFilePicker)
.InitialFileName = "C\:Extract\sales*.xlsx"
If .Show = 0 Then Exit Sub
A = .SelectedItems(1)
End With
If A = False Or IsEmpty(A) Then Exit Sub
Application.ScreenUpdating = False
Set nb = Workbooks.Open(A)
ThisWorkbook.Activate
nb.Sheets(1).Range("A1:AD2000").Copy
rngDestination.PasteSpecial Paste:=xlPasteValues
rngDestination.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
nb.Close savechanges:=False
End Sub