save only active sheet Button.
 

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

save only active sheet Button.
 

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)





"tim" wrote in message
...
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

save only active sheet Button.
 

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

On Thu, 22 May 2008 15:14:57 +0100, "tim" wrote:

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

save only active sheet Button.
 

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


"Chip Pearson" wrote in message
...
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)





"tim" wrote in message
...
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

save only active sheet Button.
 

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

regards

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Thu, 22 May 2008 15:14:57 +0100, "tim"
wrote:

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

save only active sheet Button.
 

Delete the Application.DisplayAlerts lines.


Gord

On Fri, 23 May 2008 00:53:37 +0100, "tim" wrote:

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

regards

"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
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

On Thu, 22 May 2008 15:14:57 +0100, "tim"
wrote:

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

save only active sheet Button.
 

thanks. it does ask now if file exist.

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Delete the Application.DisplayAlerts lines.


Gord

On Fri, 23 May 2008 00:53:37 +0100, "tim"
wrote:

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

regards

"Gord Dibben" <gorddibbATshawDOTca wrote in message
. ..
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

On Thu, 22 May 2008 15:14:57 +0100, "tim"
wrote:

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

save only active sheet Button.
 

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


Gord

On Fri, 23 May 2008 08:38:21 +0100, "tim" wrote:

thanks. it does ask now if file exist.

"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Delete the Application.DisplayAlerts lines.


Gord

On Fri, 23 May 2008 00:53:37 +0100, "tim"
wrote:

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

regards

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Thu, 22 May 2008 15:14:57 +0100, "tim"
wrote:

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