I want to change to directory to default to C:\My Documents, but it defaults to C:\Extract when running Macro below
It would be appreciated if someone could kindly assist me
Code:
Sub Open_Statement()
ChDir "C:\My Documents\"
Clear_Data
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
Sheets("Import Data").Select
Set ts = ActiveSheet
On Error Resume Next
Set rngDestination = ts.[A1]
On Error GoTo 0
If rngDestination Is Nothing Then Exit Sub
MsgBox "Select Statement for this branch"
With Application.FileDialog(msoFileDialogFilePicker)
.InitialFileName = "*Statement*.xlsm"
If .Show = 0 Then Exit Sub
A = .SelectedItems(1)
End With
' A = Application.GetOpenFilename
' If A = False Or IsEmpty(A) Then Exit Sub
Application.ScreenUpdating = False
Set nb = Workbooks.Open(A)
ThisWorkbook.Activate
nb.Sheets(2).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
Code:
Sub Clear_Data()
Dim LR As Long
With Sheets("Import Data")
LR = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("a1:Z" & LR).ClearContents
.Range("a1:Z" & LR).ClearFormats
End With
End Sub
It would be appreciated if someone could kindly assist me