| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Command Button Active X controls | Excel Discussion (Misc queries) | |||
| Command Button Save As Application.ExecuteExcel4Macro ("SAVE.AS?() | Excel Discussion (Misc queries) | |||
| Form Button not active | Excel Discussion (Misc queries) | |||
| Need help, Seq numbers, Save filename & A button on sheet. | Excel Discussion (Misc queries) | |||
| how to get disk icon on save button of save as dialog like 2000 | Excel Discussion (Misc queries) | |||
Hybrid Mode
