Macro to Save just one sheet to new workbook.

G

Guy

I need a macro that when selected will copy the currently displayed sheet of
a workbook to a new workbook *(with only the values, no macros or formulas),
and then, if possible, to also automatically bring up the 'Save As' window
with the current date as the name, formatted as (mm-dd-yy).

Thanks ya'll,
Guy
 
J

Joel

ActiveSheet.Copy
Set newbk = ActiveWorkbook
newbk.ActiveSheet.Cells.Copy
newbk.ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues

InitialName = Format(Date, "mm-dd-yy") & ".xls"
FName = Application.GetSaveAsFilename(InitialFileName:=InitialName, _
fileFilter:="Excel Files (*.xls), *.xls")
newbk.SaveAs Filename:=FName
 
G

Guy

That is perfect!
Thanks a bunch,
Guy

Joel said:
ActiveSheet.Copy
Set newbk = ActiveWorkbook
newbk.ActiveSheet.Cells.Copy
newbk.ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues

InitialName = Format(Date, "mm-dd-yy") & ".xls"
FName = Application.GetSaveAsFilename(InitialFileName:=InitialName, _
fileFilter:="Excel Files (*.xls), *.xls")
newbk.SaveAs Filename:=FName
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro to Insert Current Date into cell - Macro to "Save As" 4
How do I lookup the sheetname in a workbook 2
Save all sheets in a new workbook 7
Comment with date stamp, again 1
Troubleshooting macro to save in Compatability Mode 2
Convert worksheet string name to date format in cell 2
failure to update conditional formatting 2
Sheet selected, automatic return to top of sheet 7

Top