Default File Path in a Macro

[ebrandt]

I have written a macro that copies worksheets from a workbook to a new workbook and saves it.

I want the new workbook to save to the same network file folder as the original.

It seems like everytime i run the macro it saves in a different location. Sometimes in the correct location. Often saves it to My Documents on my hard drive.

How do I write the macro to identify the file path of the current document and then save the new workbook to the same location?

 

[jim may]

Dim MyCurrPath As String
MyCurrPath = ThisWorkbook.Path
'Your Code to Copy to New Wb

 

[ebrandt]

Thanks Jim,

That looks like the answer. I figured it was something simple. I will give this a try.

 

[jim may]

Here's another go at it..

Sub Foo()
Dim MyDir As String
MyDir = CurDir

'your code here

End Sub

 

[ebrandt]

Macro stopped on this statement;

ActiveWorkbook.SaveAs Filename:= _
MyCurrPath \ "Sub Analysis Sheet - " & Estimator & ".xlsm" _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

I assume I wrote this incorrectly??

 

[jim may]

Try..

ActiveWorkbook.SaveAs Filename:= _
MyCurrPath & "\" & "Sub Analysis Sheet - Estimator" & ".xlsm" _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

 

[ebrandt]

That worked. Thanks Jim!