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
Dim/ReDim/Conststatement at the procedure level.) You need to scope these at module or global level, or instead to pass between the procedures.Public LocalPath As Varianton top ofSub ReadLocalPath(), now everything works. THANKS GUYS!