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
3
  • 1
    is localpath a public var? Commented Nov 1, 2016 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. Commented Nov 1, 2016 at 14:54
  • I declared Public LocalPath As Variant on top of Sub ReadLocalPath(), now everything works. THANKS GUYS! Commented Nov 1, 2016 at 15:00

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.