Save Sheet w/extra features

[FoRmEd]

Hello everyone:

I located the following codes on our site and would like to merge them all and add just one more thing.

First I want to save a single sheet:

Sub savesheet2()
Application.ScreenUpdating = False
ActiveSheet.Select
ActiveSheet.Copy
ThisFile = Range("A1").Value
ActiveSheet.SaveAs Filename:="C:temp" & ThisFile & ".xls"
Application.ScreenUpdating = True
ActiveWorkbook.Close
End Sub

Then, if the destination folder doesn't exist, it needs to be created.

Function fileexists(f As String) As Boolean
If Not f = vbNullString Then fileexists = True
 
End Function

Lastly I want it to create a custom save as name "PHZ2[todays date].xls"
"today's date" would be pulled from a populated cell like N1

no code just yet

Your help is in merging these is much appreciated

 

[VoG]

Try

Sub savesheet2()
Application.ScreenUpdating = False
ActiveSheet.Copy
If Not DirExists("C:\temp") Then MkDir "C:\temp"
ActiveSheet.SaveAs Filename:="C:\temp\" & "PHZ2 " & Format(Date, "mm-dd-yyyy") & ".xls", FileFormat:=56
Application.ScreenUpdating = True
ActiveWorkbook.Close
End Sub


Function DirExists(SDirectory As String) As Boolean
DirExists = Dir(SDirectory, vbDirectory) <> ""
End Function

 

[FoRmEd]

It won't create the new directory. Then I skipped that debug and got a runtime-error 104 on the code below.

ActiveSheet.SaveAs FileName:="I:\backup\PHZ2\" & "PHZ2 " & Format_
(Date, "mm-dd-yyyy") & ".xls", FileFormat:=56

 

[VoG]

Working here. Try without the fileformat argument

Sub savesheet2()
Application.ScreenUpdating = False
ActiveSheet.Copy
If Not DirExists("C:\temp") Then MkDir "C:\temp"
ActiveSheet.SaveAs Filename:="C:\temp\" & "PHZ2 " & Format(Date, "mm-dd-yyyy") & ".xls"
Application.ScreenUpdating = True
ActiveWorkbook.Close
End Sub


Function DirExists(SDirectory As String) As Boolean
DirExists = Dir(SDirectory, vbDirectory) <> ""
End Function

 

[FoRmEd]

Thank you very much for the help, your dedication doesn't go unnoticed
Pulled out the (FileFormat:=56) and now getting

Run-time error '76'
Path not found

I'm going to read-up on this error; try and understand it by the time you post, so I will your code

I had a quick idea but it failed. I thought the workbook being password protected would effect it so I added the respective code below (this didn't fix the run-time error). And below that is what the code looks like now.

     ActiveSheet.Unprotect Password:="****"
   'code going here
     ActiveSheet.Protect Password:="****"

Sub savesheet2()
ActiveSheet.Unprotect Password:="****"
Application.ScreenUpdating = False
ActiveSheet.Copy
If Not DirExists("I:\backup\PHZ2") Then MkDir "I:\backup\PHZ2"
ActiveSheet.SaveAs FileName:="I:\backup\PHZ2\" & "PHZ2 " & Format(Date, "mm-dd-yyyy") & ".xls"
Application.ScreenUpdating = True
ActiveWorkbook.Close
ActiveSheet.Protect Password:="*****"
End Sub
 
Function DirExists(SDirectory As String) As Boolean
DirExists = Dir(SDirectory, vbDirectory) <> ""
End Function