VBA to save AND save as using command button and display message box

[charliew]

Hello,

I am trying to get a workbook to save using a command button so I can get a weekly log of what has changed in the spreadsheet. I need the "master" spreadsheet to save AND a separate workbook saving with the filename of the date it was saved...I can't get the first bit to work, so it saves a copy with the correct filename and displays the message box, but i want the original workbook to save as well...
So i will always have a workbook called "flam bay" which is the copy that is the working copy...and then a weekly workbook so i can see the stock changes in that bay.

I hope that makes sense...

Private Sub CommandButton1_Click()


Dim CurrDate As String

CurrDate = Format(Date, "DD.MM.YY")

ActiveWorkbook.SaveAs Filename:="U:\Operations, Compliance, SHE & Quality\Technical\Chemists\Flam Bay\" & fName & "\Flam Bay" & fName & " - " & CurrDate & ".xlsm" _

MsgBox "Workbook Saved!", vbInformation, "Check"

End Sub

I'd also like it to email a copy under the same filename to a specified email address...but i don't know where to start with this....

Thank you so much in advance...

 

[jkpieterse]

Rather than SaveAs, use SaveCopyAs , followed by ActiveWorkbook.Save.

 

[charliew]

Thank you so much...i can't get this to work either!

I'm getting a "run-time 424" error "object required"

SaveCopyAs.ActiveWorkbook.SaveAs Filename:="U:\Operations, Compliance, SHE & Quality\Technical\Chemists\Flam Bay\" & fName & "\Flam Bay" & fName & " - " & CurrDate & ".xlsm" _

 

[jkpieterse]

Instead of
SaveCopyAs.ActiveWorkbook.SaveAs
write
ActiveWorkbook.SaveCopyAs

 

[charliew]

Run-time error 1004

Private Sub CommandButton1_Click()


Dim CurrDate As String

CurrDate = Format(Date, "DD.MM.YY")

ActiveWorkbook.SaveCopyAs Filename:="U:\Operations, Compliance, SHE & Quality\Technical\Chemists\Flam Bay\" & fName & "\Flam Bay" & fName & " - " & CurrDate & ".xlsm" _

MsgBox "Workbook Saved!", vbInformation, "Check"

End Sub

 

[jkpieterse]

Error 1004 is rther generic. What is the description of the error?

 

[charliew]

Sorry!

the file hasn't moved or changed name...I'm having a play with it but can't get it to work...

 

[charliew]

i figured it out...didn't notice the extra /

do you know how i can get it to email a copy as well?

 

[jkpieterse]

You could try the SendMail method of the workbook:

ActiveWorkbook.SendMail "someone@yourcompany.com", "Sending you this workbook"