PDA

View Full Version : Macro to save spreedsheet as new file



mtrilce
11-24-2016, 12:56 AM
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
11-24-2016, 05:25 AM
Sub test()

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

End Sub

mtrilce
11-24-2016, 08:23 AM
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
11-24-2016, 09:54 PM
"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
11-25-2016, 10:32 AM
Thanks so much jolivanes... I will explain myself better in future queries ... How could I add to this macro a MsgBox "Saved successfully"?

jolivanes
11-25-2016, 12:26 PM
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
11-26-2016, 03:32 PM
Thanks so much Jolivanes and Mana

bests regards

Marcela

mtrilce
12-08-2016, 11:22 AM
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
12-08-2016, 12:25 PM
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
12-08-2016, 01:02 PM
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
12-08-2016, 01:31 PM
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
12-08-2016, 02:30 PM
That´s great, thanks Jolivanes


Marcela

jolivanes
12-08-2016, 03:55 PM
Good luck with it Marcela.
And please remember the code tags next time.
Have a pleasant weekend