Save Current workbook in new folder

[Siderman]

Hey Folks,

I have been searching relentlesly for a simple code to save open active workbook as value in cell a1 in a new folder named as value in cell a1 on desktop of whomever is using workbook.

P.S. Thanks for all the great tips from everyone out there

 

[taurean]

Welcome to MrExcel.

You have not mentioned OS you are using. I am using XP Sp3 and the following code works. I wouldn't be sure if the same applies to Win 7.

Public Sub SaveOnDesktop()
Dim strPath As String, strFile As String
'Build Path
strPath = Environ$("HOMEPATH") & Application.PathSeparator & "Desktop" & _
Application.PathSeparator & Range("A1").Value
'Make Folder
MkDir strPath
'Build File Name
strFile = strPath & Application.PathSeparator & Range("A1").Value
'Save Activeworkbook
ActiveWorkbook.SaveAs strFile, 51 '51 is for default
End Sub

 

[Siderman]

Sorry about lack of info. My OS is Vista

 

[taurean]

Sorry about lack of info. My OS is Vista

Please try the code and see if it works.

 

[Siderman]

the code worked perfect !! it doesnt save as a macro enabled workbook though. i guess i should have indicated that.
Thanks for the quick responce!

 

[Siderman]

should i be changing the 51 to 53 or something?

 

[taurean]

the code worked perfect !! it doesnt save as a macro enabled workbook though. i guess i should have indicated that.
Thanks for the quick responce!

Oh that is good. Just change 51 (xlsx) to 52 (xlsm).

 

[Siderman]

That worked great! would it be easy to make it close the workbook also?

 

[taurean]

How are you running this code?

Does above code reside in the "activeworkbook" itself or you are running it from a separate workbook?

 

[Siderman]

the active work book only