save only active sheet Button.

  • Thread starter Thread starter tim
  • Start date Start date
T

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
 
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)
 
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
 
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
 
Thanks gord
There was only one drawback. It does not ask to replace if the file name
already exist.

regards
 
Back
Top