VBA SaveAs to New Drive, use cell ref as name, and Password Protect document

[clvincent]

Need help please - Just learning VBA. Trying to write macro that will save file to orig location (not protected), then SAVEAS to new folder location, rename using cell CE3 as name along with time saved, and password protect this document (not original). Macro won't work - what am I doing wrong?

Sub SubmitReport()
'
' SubmitReport Macro
'
'
Sheets("ATB").Select
ActiveSheet.Unprotect
fName = Range("CE3") + " " + Format(Now(), "h:mm;@")
ThisWorkbook.SaveAS "F:\pataccts\SECRETARY\", fName, Password:="myatb",
WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWorkbook.Close True
End Sub

​

<!-- end of AOLMsgPart_1_2c2cc7f0-476b-4900-b76c-82b250d49b36 --><STYLE>.AOLWebSuite .AOLPicturesFullSizeLink { height: 1px; width: 1px; overflow: hidden; } .AOLWebSuite a {color:blue; text-decoration: underline; cursor: pointer} .AOLWebSuite a.hsSig {cursor: default}</STYLE>

 

[VoG]

Perhaps this

Sub SubmitReport()
'
' SubmitReport Macro
'
'
Sheets("ATB").Select
ActiveSheet.Unprotect
fName = Range("CE3").Value & " " & Format(Time, "h-mm") & ".xls"
ThisWorkbook.SaveAs Filename:="F:\pataccts\SECRETARY\" & fName, Password:="myatb", _
WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close True
End Sub

 

[clvincent]

Thank you - I will give it a try tomorrow (at work). I assume the file format would be xlsm however, would it now? macro enabled?

 

[VoG]

If 2007-2010 then

Sub SubmitReport()
'
' SubmitReport Macro
'
'
Sheets("ATB").Select
ActiveSheet.Unprotect
fName = Range("CE3").Value & " " & Format(Time, "h-mm") & ".xlsm"
ThisWorkbook.SaveAs Filename:="F:\pataccts\SECRETARY\" & fName, FileFormat:=52, Password:="myatb", _
WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close True
End Sub

 

[cvincent]

Thank you. Have not had a chance to get back to my project yet, but will give it a try & post how it works.