Macro does not change to Directory Set

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,996
Office Version
  1. 2019
Platform
  1. Windows
I want to change to directory to default to C:\My Documents, but it defaults to C:\Extract when running Macro below

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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,627
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
.InitialFileName = "C:\My Documents\*Statement*.xlsm"
 
Solution

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,996
Office Version
  1. 2019
Platform
  1. Windows
Thanks for the help-Code works perfectly
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,627
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,143,637
Messages
5,719,969
Members
422,252
Latest member
wannabegeek1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top