save only active sheet Button.

[tim]

Hi
I would like to make a button that would save only active sheet and get
name from cell "C5".
i.e. save active sheet "Invoice" only and name from cell "C5" which invoice
number "55"
Result, File saved 55.xls with only one sheet "invoice".

any suggestion please.

regards

 

[Chip Pearson]

Try

Dim S As String
S = Range("C5").Text
If S = vbNullString Then
Exit Sub
End If
Application.EnableEvents = False
ActiveSheet.Copy ' creates and activates new workbook
ActiveWorkbook.SaveAs S &".xls"
Application.EnableEvents = True


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Gord Dibben]

Sub Make_New_Book()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Invoice").Copy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path _
& "\" & Range("C5").Value
ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

No error-checking for blank or illegal characters in C5


Gord Dibben MS Excel MVP

 

[tim]

Thanks
It Works for me. Just one thing it changes some cells back gound color when
it is saved.

Additionally
1- How to remove the save button from saved file.
2- Save the file to "c:\customes_invoices" (at the moment it saves it on
desktop)


regards

 

[tim]

Thanks gord
There was only one drawback. It does not ask to replace if the file name
already exist.

regards

 

[Gord Dibben]

Delete the Application.DisplayAlerts lines.


Gord

 

[tim]

thanks. it does ask now if file exist.

Delete the Application.DisplayAlerts lines.


Gord

 

[Gord Dibben]

Apologies for not telling you of the automatic over-write in the beginning.


Gord