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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.