0

I'm trying to save a worksheet into a local folder. When I use this code, everything works well:

Sub SavePrepAs()
Workbooks("Robot Model.xlsm").Activate
LocalPath = ActiveWorkbook.Worksheets("Preparation").Range("B6").Value
If Right(LocalPath, 1) <> "\" Then LocalPath = LocalPath & "\"

Workbooks("Robot Model.xlsm").Worksheets("Preparation").Copy
With ActiveWorkbook
    .SaveAs LocalPath & .Sheets(1).Name
    .Close 0
End With
End Sub

But when I try to split the code into two sub, the file is saved into my H drive instead of a sub folder of H drive as I indicated in B6 cell. Why?

Sub ReadLocalPath()

'Read Local Path from the sheet
Workbooks("Robot Model.xlsm").Activate
LocalPath = ActiveWorkbook.Worksheets("Preparation").Range("B6").Value
If Right(LocalPath, 1) <> "\" Then LocalPath = LocalPath & "\"
Debug.Print LocalPath
End Sub

Sub SavePrepAs()
Call ReadLocalPath

Workbooks("Robot Model.xlsm").Worksheets("Preparation").Copy
With ActiveWorkbook
    .SaveAs LocalPath & .Sheets(1).Name
    .Close 0
End With
Debug.Print LocalPath
End Sub
Community
  • 1
  • 1
vivi11130704
  • 431
  • 8
  • 21
  • 1
    is localpath a public var? – Nathan_Sav Nov 01 '16 at 14:49
  • 1
    In the code you've provided, you haven't declared any of your variables. This is a big no-no, for a number of reasons that I won't elaborate here. In any case, when a variable isn't declared explicitly, it's scope is local only to the procedure where it's instantiated, and it goes out of scope when that procedure stops running. (This is also true for any variable which is declared with a `Dim/ReDim/Const` statement at the procedure level.) You need to scope these at module or global level, or instead to pass between the procedures. – David Zemens Nov 01 '16 at 14:54
  • I declared `Public LocalPath As Variant` on top of `Sub ReadLocalPath()`, now everything works. THANKS GUYS! – vivi11130704 Nov 01 '16 at 15:00

0 Answers0