Macro to save spreedsheet as new file

[mtrilce]

Hello friends,


This is the case: I have an excel book with two sheets: one called "begin" and the other called "Report". What I need is to place a button on the "begin" sheet and assign it a macro to save the "Report" sheet as a new file in the same folder and with the same format, with the name "report + date today"


If someone can help me, I will thank you very much

Marcela

[mana]

Sub test()
    
    ThisWorkbook.Worksheets("Report").Copy
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\report" & Format(Date, "ddmmyyyy") & ".xlsx"
        
End Sub

[mtrilce]

Thanks very much Mana...

It does not work.... Could you give me an example please? I´m sorry to ask that, but I´m really a beginner about macros...

Marcela

[jolivanes]

"It does not work" does not tell us anything at all.
I'll take the liberty to add an example. I hope mana does not mind.

[mtrilce]

Thanks so much jolivanes... I will explain myself better in future queries ... How could I add to this macro a MsgBox "Saved successfully"?

[jolivanes]

Just before

End Sub

add this

MsgBox "Workbook saved in " & ThisWorkbook.Path & " as ""Report for " & Format(Date, "dd-mm-yyyy") & ".xlsx"""

or just this

MsgBox "Saved successfully!"

so you'll end up with something like this

Sub test()
    Application.ScreenUpdating = False
    ThisWorkbook.Worksheets("Report").Copy
        ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Report for " & Format(Date, "dd-mm-yyyy") & ".xlsx"
    ActiveWorkbook.Close
    Application.ScreenUpdating = True
    MsgBox "Workbook saved in " & ThisWorkbook.Path & " as ""Report for " & Format(Date, "dd-mm-yyyy") & ".xlsx"""
End Sub

[mtrilce]

Thanks so much Jolivanes and Mana

bests regards

Marcela

[mtrilce]

Dear friends, I come back to you on this same question to know how I can make this macro work when the "Report" sheet is hidden
Thank you very much

[jolivanes]

Before the line where you copy Worksheet("Report"), add this line

    ThisWorkbook.Worksheets("Report").Visible = True

and after the line where you save the Workbook as an .xlsx file, add this line

    ThisWorkbook.Worksheets("Report").Visible = False

You don't show us the actual code you're working with so it's a guess.

[mtrilce]

Hi Jolivanes,

I´m working with this code:

Sub test()
Application.ScreenUpdating = False
ThisWorkbook.Worksheets("Report").Copy
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Report for " & Format(Date, "dd-mm-yyyy") & ".xlsx"
ActiveWorkbook.Close
Application.ScreenUpdating = True
MsgBox "Workbook saved in " & ThisWorkbook.Path & " as ""Report for " & Format(Date, "dd-mm-yyyy") & ".xlsx"""
End Sub


Tranks for helping me

[jolivanes]

Please add code tags around your code. Select (highlight) your code and click on the pound (#) sign.

Sub test()
Application.ScreenUpdating = False
ThisWorkbook.Worksheets("Report").Visible = True
ThisWorkbook.Worksheets("Report").Copy
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Report for " & Format(Date, "dd-mm-yyyy") & ".xlsx"
ActiveWorkbook.Close
ThisWorkbook.Worksheets("Report").Visible = False
Application.ScreenUpdating = True
MsgBox "Workbook saved in " & ThisWorkbook.Path & " as ""Report for " & Format(Date, "dd-mm-yyyy") & ".xlsx"""
End Sub

Obviously you don't need a password otherwise you would have mentioned it.

[mtrilce]

That´s great, thanks Jolivanes


Marcela

[jolivanes]

Good luck with it Marcela.
And please remember the code tags next time.
Have a pleasant weekend