|
product date stamp file save
I am hoping with your programming skills, by way of a coded macro, to save an
exel file by operating a button and achieving the following objective automatically... name file as... customer_date_time.xls and save file to specific path for example c:\archive\product A --- please note customer name is variable - which is selected from a drop down list in a specific cell in an order form. date & time is real time The time variable is required and preferable as it is possible for 1 customer to take 2 different products on the same date. One cannot have to two file names the same!. For example : customer A_301006.xls (X2) wont work but customer A_301006_1000.xls and customer A_301006_1005 will save without conflict ---- Background information... I have 3 order forms set out vertically on a single spreadsheet covering 1 specific product. There is more than 1 product! (Product A-Z). All 3 order forms on the Product A spreadsheet are for the same product. All 3 order forms can be used for different customers selected from a drop down list in a specific cell located within the actual order form. The individual orders are only saved to a specific archive product location folder manually at the moment! (example c:\archive\product A) once customer has recieved the product. As all 3 order forms are on 1 spreadsheet I dont want to save all 3 simultaneoulsly, otherwise I will unnecessarily save duplicate orders! and create unnecessarily large files. I would only like to save the respective cells to cover each order form individually ie I will require an individual save button located side on to each order form. The forms are already cleared by way of a simple macro to continually re use. Thank you for reading my request Robert |
product date stamp file save
Create a button in the usual way and assign to it the folllowing macro:
Sub loxley() n = InputBox("Enter customer name: ") n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub When the button is pressed, the user will be prompted for the customer name. The file will then be save. -- Gary's Student "Robert Loxley" wrote: I am hoping with your programming skills, by way of a coded macro, to save an exel file by operating a button and achieving the following objective automatically... name file as... customer_date_time.xls and save file to specific path for example c:\archive\product A --- please note customer name is variable - which is selected from a drop down list in a specific cell in an order form. date & time is real time The time variable is required and preferable as it is possible for 1 customer to take 2 different products on the same date. One cannot have to two file names the same!. For example : customer A_301006.xls (X2) wont work but customer A_301006_1000.xls and customer A_301006_1005 will save without conflict ---- Background information... I have 3 order forms set out vertically on a single spreadsheet covering 1 specific product. There is more than 1 product! (Product A-Z). All 3 order forms on the Product A spreadsheet are for the same product. All 3 order forms can be used for different customers selected from a drop down list in a specific cell located within the actual order form. The individual orders are only saved to a specific archive product location folder manually at the moment! (example c:\archive\product A) once customer has recieved the product. As all 3 order forms are on 1 spreadsheet I dont want to save all 3 simultaneoulsly, otherwise I will unnecessarily save duplicate orders! and create unnecessarily large files. I would only like to save the respective cells to cover each order form individually ie I will require an individual save button located side on to each order form. The forms are already cleared by way of a simple macro to continually re use. Thank you for reading my request Robert |
product date stamp file save
Thank you Gary!
Is there any way... 1 - that the customer variable which is available from within my order form can be used automatically to create the complete file name rather than the operator completing that part manually? 2 - as there are 3 order forms located vertically per sheet i preferably would only like to save the 1 order for per save function rather than 3. is there any way only the cell range for each form can be saved? Thank you "Gary''s Student" wrote: Create a button in the usual way and assign to it the folllowing macro: Sub loxley() n = InputBox("Enter customer name: ") n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub When the button is pressed, the user will be prompted for the customer name. The file will then be save. -- Gary's Student "Robert Loxley" wrote: I am hoping with your programming skills, by way of a coded macro, to save an exel file by operating a button and achieving the following objective automatically... name file as... customer_date_time.xls and save file to specific path for example c:\archive\product A --- please note customer name is variable - which is selected from a drop down list in a specific cell in an order form. date & time is real time The time variable is required and preferable as it is possible for 1 customer to take 2 different products on the same date. One cannot have to two file names the same!. For example : customer A_301006.xls (X2) wont work but customer A_301006_1000.xls and customer A_301006_1005 will save without conflict ---- Background information... I have 3 order forms set out vertically on a single spreadsheet covering 1 specific product. There is more than 1 product! (Product A-Z). All 3 order forms on the Product A spreadsheet are for the same product. All 3 order forms can be used for different customers selected from a drop down list in a specific cell located within the actual order form. The individual orders are only saved to a specific archive product location folder manually at the moment! (example c:\archive\product A) once customer has recieved the product. As all 3 order forms are on 1 spreadsheet I dont want to save all 3 simultaneoulsly, otherwise I will unnecessarily save duplicate orders! and create unnecessarily large files. I would only like to save the respective cells to cover each order form individually ie I will require an individual save button located side on to each order form. The forms are already cleared by way of a simple macro to continually re use. Thank you for reading my request Robert |
product date stamp file save
Lets assume that the customer name is in Cell A1, then:
Sub loxley() n = Range(A1).Value n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub You can change the A1 to the proper cell. With regard to your second question, I dont know. -- Gary's Student "Robert Loxley" wrote: Thank you Gary! Is there any way... 1 - that the customer variable which is available from within my order form can be used automatically to create the complete file name rather than the operator completing that part manually? 2 - as there are 3 order forms located vertically per sheet i preferably would only like to save the 1 order for per save function rather than 3. is there any way only the cell range for each form can be saved? Thank you "Gary''s Student" wrote: Create a button in the usual way and assign to it the folllowing macro: Sub loxley() n = InputBox("Enter customer name: ") n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub When the button is pressed, the user will be prompted for the customer name. The file will then be save. -- Gary's Student "Robert Loxley" wrote: I am hoping with your programming skills, by way of a coded macro, to save an exel file by operating a button and achieving the following objective automatically... name file as... customer_date_time.xls and save file to specific path for example c:\archive\product A --- please note customer name is variable - which is selected from a drop down list in a specific cell in an order form. date & time is real time The time variable is required and preferable as it is possible for 1 customer to take 2 different products on the same date. One cannot have to two file names the same!. For example : customer A_301006.xls (X2) wont work but customer A_301006_1000.xls and customer A_301006_1005 will save without conflict ---- Background information... I have 3 order forms set out vertically on a single spreadsheet covering 1 specific product. There is more than 1 product! (Product A-Z). All 3 order forms on the Product A spreadsheet are for the same product. All 3 order forms can be used for different customers selected from a drop down list in a specific cell located within the actual order form. The individual orders are only saved to a specific archive product location folder manually at the moment! (example c:\archive\product A) once customer has recieved the product. As all 3 order forms are on 1 spreadsheet I dont want to save all 3 simultaneoulsly, otherwise I will unnecessarily save duplicate orders! and create unnecessarily large files. I would only like to save the respective cells to cover each order form individually ie I will require an individual save button located side on to each order form. The forms are already cleared by way of a simple macro to continually re use. Thank you for reading my request Robert |
product date stamp file save
Thanks Gary
i replaced the macro with your suggestion following message appeared... run time error '1004' method 'range' of object' global failed. first line of code highlighed in yellow color - thats on the line with the cell reference does that mean anything to you? thank you "Gary''s Student" wrote: Lets assume that the customer name is in Cell A1, then: Sub loxley() n = Range(A1).Value n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub You can change the A1 to the proper cell. With regard to your second question, I dont know. -- Gary's Student "Robert Loxley" wrote: Thank you Gary! Is there any way... 1 - that the customer variable which is available from within my order form can be used automatically to create the complete file name rather than the operator completing that part manually? 2 - as there are 3 order forms located vertically per sheet i preferably would only like to save the 1 order for per save function rather than 3. is there any way only the cell range for each form can be saved? Thank you "Gary''s Student" wrote: Create a button in the usual way and assign to it the folllowing macro: Sub loxley() n = InputBox("Enter customer name: ") n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub When the button is pressed, the user will be prompted for the customer name. The file will then be save. -- Gary's Student "Robert Loxley" wrote: I am hoping with your programming skills, by way of a coded macro, to save an exel file by operating a button and achieving the following objective automatically... name file as... customer_date_time.xls and save file to specific path for example c:\archive\product A --- please note customer name is variable - which is selected from a drop down list in a specific cell in an order form. date & time is real time The time variable is required and preferable as it is possible for 1 customer to take 2 different products on the same date. One cannot have to two file names the same!. For example : customer A_301006.xls (X2) wont work but customer A_301006_1000.xls and customer A_301006_1005 will save without conflict ---- Background information... I have 3 order forms set out vertically on a single spreadsheet covering 1 specific product. There is more than 1 product! (Product A-Z). All 3 order forms on the Product A spreadsheet are for the same product. All 3 order forms can be used for different customers selected from a drop down list in a specific cell located within the actual order form. The individual orders are only saved to a specific archive product location folder manually at the moment! (example c:\archive\product A) once customer has recieved the product. As all 3 order forms are on 1 spreadsheet I dont want to save all 3 simultaneoulsly, otherwise I will unnecessarily save duplicate orders! and create unnecessarily large files. I would only like to save the respective cells to cover each order form individually ie I will require an individual save button located side on to each order form. The forms are already cleared by way of a simple macro to continually re use. Thank you for reading my request Robert |
product date stamp file save
Sorry, it also failed for me. This works:
Sub loxley() Dim n As String Dim r As Range Set r = Cells(1, 1) n = r.Value n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub -- Gary''s Student "Robert Loxley" wrote: Thanks Gary i replaced the macro with your suggestion following message appeared... run time error '1004' method 'range' of object' global failed. first line of code highlighed in yellow color - thats on the line with the cell reference does that mean anything to you? thank you "Gary''s Student" wrote: Lets assume that the customer name is in Cell A1, then: Sub loxley() n = Range(A1).Value n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub You can change the A1 to the proper cell. With regard to your second question, I dont know. -- Gary's Student "Robert Loxley" wrote: Thank you Gary! Is there any way... 1 - that the customer variable which is available from within my order form can be used automatically to create the complete file name rather than the operator completing that part manually? 2 - as there are 3 order forms located vertically per sheet i preferably would only like to save the 1 order for per save function rather than 3. is there any way only the cell range for each form can be saved? Thank you "Gary''s Student" wrote: Create a button in the usual way and assign to it the folllowing macro: Sub loxley() n = InputBox("Enter customer name: ") n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub When the button is pressed, the user will be prompted for the customer name. The file will then be save. -- Gary's Student "Robert Loxley" wrote: I am hoping with your programming skills, by way of a coded macro, to save an exel file by operating a button and achieving the following objective automatically... name file as... customer_date_time.xls and save file to specific path for example c:\archive\product A --- please note customer name is variable - which is selected from a drop down list in a specific cell in an order form. date & time is real time The time variable is required and preferable as it is possible for 1 customer to take 2 different products on the same date. One cannot have to two file names the same!. For example : customer A_301006.xls (X2) wont work but customer A_301006_1000.xls and customer A_301006_1005 will save without conflict ---- Background information... I have 3 order forms set out vertically on a single spreadsheet covering 1 specific product. There is more than 1 product! (Product A-Z). All 3 order forms on the Product A spreadsheet are for the same product. All 3 order forms can be used for different customers selected from a drop down list in a specific cell located within the actual order form. The individual orders are only saved to a specific archive product location folder manually at the moment! (example c:\archive\product A) once customer has recieved the product. As all 3 order forms are on 1 spreadsheet I dont want to save all 3 simultaneoulsly, otherwise I will unnecessarily save duplicate orders! and create unnecessarily large files. I would only like to save the respective cells to cover each order form individually ie I will require an individual save button located side on to each order form. The forms are already cleared by way of a simple macro to continually re use. Thank you for reading my request Robert |
product date stamp file save
Gary
It works a treat. I will let you have the rest of the day off! I am going to puzzle out the last part of what I was hoping to achieve with reference to only saving an individual order form rather than all 3 in one go. Thank you again "Gary''s Student" wrote: Sorry, it also failed for me. This works: Sub loxley() Dim n As String Dim r As Range Set r = Cells(1, 1) n = r.Value n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub -- Gary''s Student "Robert Loxley" wrote: Thanks Gary i replaced the macro with your suggestion following message appeared... run time error '1004' method 'range' of object' global failed. first line of code highlighed in yellow color - thats on the line with the cell reference does that mean anything to you? thank you "Gary''s Student" wrote: Lets assume that the customer name is in Cell A1, then: Sub loxley() n = Range(A1).Value n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub You can change the A1 to the proper cell. With regard to your second question, I dont know. -- Gary's Student "Robert Loxley" wrote: Thank you Gary! Is there any way... 1 - that the customer variable which is available from within my order form can be used automatically to create the complete file name rather than the operator completing that part manually? 2 - as there are 3 order forms located vertically per sheet i preferably would only like to save the 1 order for per save function rather than 3. is there any way only the cell range for each form can be saved? Thank you "Gary''s Student" wrote: Create a button in the usual way and assign to it the folllowing macro: Sub loxley() n = InputBox("Enter customer name: ") n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub When the button is pressed, the user will be prompted for the customer name. The file will then be save. -- Gary's Student "Robert Loxley" wrote: I am hoping with your programming skills, by way of a coded macro, to save an exel file by operating a button and achieving the following objective automatically... name file as... customer_date_time.xls and save file to specific path for example c:\archive\product A --- please note customer name is variable - which is selected from a drop down list in a specific cell in an order form. date & time is real time The time variable is required and preferable as it is possible for 1 customer to take 2 different products on the same date. One cannot have to two file names the same!. For example : customer A_301006.xls (X2) wont work but customer A_301006_1000.xls and customer A_301006_1005 will save without conflict ---- Background information... I have 3 order forms set out vertically on a single spreadsheet covering 1 specific product. There is more than 1 product! (Product A-Z). All 3 order forms on the Product A spreadsheet are for the same product. All 3 order forms can be used for different customers selected from a drop down list in a specific cell located within the actual order form. The individual orders are only saved to a specific archive product location folder manually at the moment! (example c:\archive\product A) once customer has recieved the product. As all 3 order forms are on 1 spreadsheet I dont want to save all 3 simultaneoulsly, otherwise I will unnecessarily save duplicate orders! and create unnecessarily large files. I would only like to save the respective cells to cover each order form individually ie I will require an individual save button located side on to each order form. The forms are already cleared by way of a simple macro to continually re use. Thank you for reading my request Robert |
product date stamp file save
Gary
I found a maro written by Dave Petersen which amongst other functions saves a defined cell range rather than the whole spreadsheet to a specific file. if you visit...will see what I mean http://www.microsoft.com/office/comm...0-80ff66ce0ed5 Would you be able to adapt part of that code to include in your brilliantly written code to save a cell range from (A1 to H40) Thats the top order form. which will be saved to top left hand corner of saved order form remembering that there are 2 other form templates which are further down the spreadsheet. I would appreciate it if you could help again! "Robert Loxley" wrote: Gary It works a treat. I will let you have the rest of the day off! I am going to puzzle out the last part of what I was hoping to achieve with reference to only saving an individual order form rather than all 3 in one go. Thank you again "Gary''s Student" wrote: Sorry, it also failed for me. This works: Sub loxley() Dim n As String Dim r As Range Set r = Cells(1, 1) n = r.Value n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub -- Gary''s Student "Robert Loxley" wrote: Thanks Gary i replaced the macro with your suggestion following message appeared... run time error '1004' method 'range' of object' global failed. first line of code highlighed in yellow color - thats on the line with the cell reference does that mean anything to you? thank you "Gary''s Student" wrote: Lets assume that the customer name is in Cell A1, then: Sub loxley() n = Range(A1).Value n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub You can change the A1 to the proper cell. With regard to your second question, I dont know. -- Gary's Student "Robert Loxley" wrote: Thank you Gary! Is there any way... 1 - that the customer variable which is available from within my order form can be used automatically to create the complete file name rather than the operator completing that part manually? 2 - as there are 3 order forms located vertically per sheet i preferably would only like to save the 1 order for per save function rather than 3. is there any way only the cell range for each form can be saved? Thank you "Gary''s Student" wrote: Create a button in the usual way and assign to it the folllowing macro: Sub loxley() n = InputBox("Enter customer name: ") n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub When the button is pressed, the user will be prompted for the customer name. The file will then be save. -- Gary's Student "Robert Loxley" wrote: I am hoping with your programming skills, by way of a coded macro, to save an exel file by operating a button and achieving the following objective automatically... name file as... customer_date_time.xls and save file to specific path for example c:\archive\product A --- please note customer name is variable - which is selected from a drop down list in a specific cell in an order form. date & time is real time The time variable is required and preferable as it is possible for 1 customer to take 2 different products on the same date. One cannot have to two file names the same!. For example : customer A_301006.xls (X2) wont work but customer A_301006_1000.xls and customer A_301006_1005 will save without conflict ---- Background information... I have 3 order forms set out vertically on a single spreadsheet covering 1 specific product. There is more than 1 product! (Product A-Z). All 3 order forms on the Product A spreadsheet are for the same product. All 3 order forms can be used for different customers selected from a drop down list in a specific cell located within the actual order form. The individual orders are only saved to a specific archive product location folder manually at the moment! (example c:\archive\product A) once customer has recieved the product. As all 3 order forms are on 1 spreadsheet I dont want to save all 3 simultaneoulsly, otherwise I will unnecessarily save duplicate orders! and create unnecessarily large files. I would only like to save the respective cells to cover each order form individually ie I will require an individual save button located side on to each order form. The forms are already cleared by way of a simple macro to continually re use. Thank you for reading my request Robert |
product date stamp file save
Sure. I'll take a look a Peterson's code this evening after the Help Center
empties out and update this post tomorrow. -- Gary's Student "Robert Loxley" wrote: Gary I found a maro written by Dave Petersen which amongst other functions saves a defined cell range rather than the whole spreadsheet to a specific file. if you visit...will see what I mean http://www.microsoft.com/office/comm...0-80ff66ce0ed5 Would you be able to adapt part of that code to include in your brilliantly written code to save a cell range from (A1 to H40) Thats the top order form. which will be saved to top left hand corner of saved order form remembering that there are 2 other form templates which are further down the spreadsheet. I would appreciate it if you could help again! "Robert Loxley" wrote: Gary It works a treat. I will let you have the rest of the day off! I am going to puzzle out the last part of what I was hoping to achieve with reference to only saving an individual order form rather than all 3 in one go. Thank you again "Gary''s Student" wrote: Sorry, it also failed for me. This works: Sub loxley() Dim n As String Dim r As Range Set r = Cells(1, 1) n = r.Value n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub -- Gary''s Student "Robert Loxley" wrote: Thanks Gary i replaced the macro with your suggestion following message appeared... run time error '1004' method 'range' of object' global failed. first line of code highlighed in yellow color - thats on the line with the cell reference does that mean anything to you? thank you "Gary''s Student" wrote: Lets assume that the customer name is in Cell A1, then: Sub loxley() n = Range(A1).Value n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub You can change the A1 to the proper cell. With regard to your second question, I dont know. -- Gary's Student "Robert Loxley" wrote: Thank you Gary! Is there any way... 1 - that the customer variable which is available from within my order form can be used automatically to create the complete file name rather than the operator completing that part manually? 2 - as there are 3 order forms located vertically per sheet i preferably would only like to save the 1 order for per save function rather than 3. is there any way only the cell range for each form can be saved? Thank you "Gary''s Student" wrote: Create a button in the usual way and assign to it the folllowing macro: Sub loxley() n = InputBox("Enter customer name: ") n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub When the button is pressed, the user will be prompted for the customer name. The file will then be save. -- Gary's Student "Robert Loxley" wrote: I am hoping with your programming skills, by way of a coded macro, to save an exel file by operating a button and achieving the following objective automatically... name file as... customer_date_time.xls and save file to specific path for example c:\archive\product A --- please note customer name is variable - which is selected from a drop down list in a specific cell in an order form. date & time is real time The time variable is required and preferable as it is possible for 1 customer to take 2 different products on the same date. One cannot have to two file names the same!. For example : customer A_301006.xls (X2) wont work but customer A_301006_1000.xls and customer A_301006_1005 will save without conflict ---- Background information... I have 3 order forms set out vertically on a single spreadsheet covering 1 specific product. There is more than 1 product! (Product A-Z). All 3 order forms on the Product A spreadsheet are for the same product. All 3 order forms can be used for different customers selected from a drop down list in a specific cell located within the actual order form. The individual orders are only saved to a specific archive product location folder manually at the moment! (example c:\archive\product A) once customer has recieved the product. As all 3 order forms are on 1 spreadsheet I dont want to save all 3 simultaneoulsly, otherwise I will unnecessarily save duplicate orders! and create unnecessarily large files. I would only like to save the respective cells to cover each order form individually ie I will require an individual save button located side on to each order form. The forms are already cleared by way of a simple macro to continually re use. Thank you for reading my request Robert |
product date stamp file save
Hi Gary
Have you had an opprtunity to have a look at the code yet? Thank you Robert Loxley "Gary''s Student" wrote: Sure. I'll take a look a Peterson's code this evening after the Help Center empties out and update this post tomorrow. -- Gary's Student "Robert Loxley" wrote: Gary I found a maro written by Dave Petersen which amongst other functions saves a defined cell range rather than the whole spreadsheet to a specific file. if you visit...will see what I mean http://www.microsoft.com/office/comm...0-80ff66ce0ed5 Would you be able to adapt part of that code to include in your brilliantly written code to save a cell range from (A1 to H40) Thats the top order form. which will be saved to top left hand corner of saved order form remembering that there are 2 other form templates which are further down the spreadsheet. I would appreciate it if you could help again! "Robert Loxley" wrote: Gary It works a treat. I will let you have the rest of the day off! I am going to puzzle out the last part of what I was hoping to achieve with reference to only saving an individual order form rather than all 3 in one go. Thank you again "Gary''s Student" wrote: Sorry, it also failed for me. This works: Sub loxley() Dim n As String Dim r As Range Set r = Cells(1, 1) n = r.Value n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub -- Gary''s Student "Robert Loxley" wrote: Thanks Gary i replaced the macro with your suggestion following message appeared... run time error '1004' method 'range' of object' global failed. first line of code highlighed in yellow color - thats on the line with the cell reference does that mean anything to you? thank you "Gary''s Student" wrote: Lets assume that the customer name is in Cell A1, then: Sub loxley() n = Range(A1).Value n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub You can change the A1 to the proper cell. With regard to your second question, I dont know. -- Gary's Student "Robert Loxley" wrote: Thank you Gary! Is there any way... 1 - that the customer variable which is available from within my order form can be used automatically to create the complete file name rather than the operator completing that part manually? 2 - as there are 3 order forms located vertically per sheet i preferably would only like to save the 1 order for per save function rather than 3. is there any way only the cell range for each form can be saved? Thank you "Gary''s Student" wrote: Create a button in the usual way and assign to it the folllowing macro: Sub loxley() n = InputBox("Enter customer name: ") n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub When the button is pressed, the user will be prompted for the customer name. The file will then be save. -- Gary's Student "Robert Loxley" wrote: I am hoping with your programming skills, by way of a coded macro, to save an exel file by operating a button and achieving the following objective automatically... name file as... customer_date_time.xls and save file to specific path for example c:\archive\product A --- please note customer name is variable - which is selected from a drop down list in a specific cell in an order form. date & time is real time The time variable is required and preferable as it is possible for 1 customer to take 2 different products on the same date. One cannot have to two file names the same!. For example : customer A_301006.xls (X2) wont work but customer A_301006_1000.xls and customer A_301006_1005 will save without conflict ---- Background information... I have 3 order forms set out vertically on a single spreadsheet covering 1 specific product. There is more than 1 product! (Product A-Z). All 3 order forms on the Product A spreadsheet are for the same product. All 3 order forms can be used for different customers selected from a drop down list in a specific cell located within the actual order form. The individual orders are only saved to a specific archive product location folder manually at the moment! (example c:\archive\product A) once customer has recieved the product. As all 3 order forms are on 1 spreadsheet I dont want to save all 3 simultaneoulsly, otherwise I will unnecessarily save duplicate orders! and create unnecessarily large files. I would only like to save the respective cells to cover each order form individually ie I will require an individual save button located side on to each order form. The forms are already cleared by way of a simple macro to continually re use. Thank you for reading my request Robert |
product date stamp file save
I apologize.
It is on my "to to" list and I will try to get to it today. Sorry again. -- Gary's Student "Robert Loxley" wrote: Hi Gary Have you had an opprtunity to have a look at the code yet? Thank you Robert Loxley "Gary''s Student" wrote: Sure. I'll take a look a Peterson's code this evening after the Help Center empties out and update this post tomorrow. -- Gary's Student "Robert Loxley" wrote: Gary I found a maro written by Dave Petersen which amongst other functions saves a defined cell range rather than the whole spreadsheet to a specific file. if you visit...will see what I mean http://www.microsoft.com/office/comm...0-80ff66ce0ed5 Would you be able to adapt part of that code to include in your brilliantly written code to save a cell range from (A1 to H40) Thats the top order form. which will be saved to top left hand corner of saved order form remembering that there are 2 other form templates which are further down the spreadsheet. I would appreciate it if you could help again! "Robert Loxley" wrote: Gary It works a treat. I will let you have the rest of the day off! I am going to puzzle out the last part of what I was hoping to achieve with reference to only saving an individual order form rather than all 3 in one go. Thank you again "Gary''s Student" wrote: Sorry, it also failed for me. This works: Sub loxley() Dim n As String Dim r As Range Set r = Cells(1, 1) n = r.Value n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub -- Gary''s Student "Robert Loxley" wrote: Thanks Gary i replaced the macro with your suggestion following message appeared... run time error '1004' method 'range' of object' global failed. first line of code highlighed in yellow color - thats on the line with the cell reference does that mean anything to you? thank you "Gary''s Student" wrote: Lets assume that the customer name is in Cell A1, then: Sub loxley() n = Range(A1).Value n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub You can change the A1 to the proper cell. With regard to your second question, I dont know. -- Gary's Student "Robert Loxley" wrote: Thank you Gary! Is there any way... 1 - that the customer variable which is available from within my order form can be used automatically to create the complete file name rather than the operator completing that part manually? 2 - as there are 3 order forms located vertically per sheet i preferably would only like to save the 1 order for per save function rather than 3. is there any way only the cell range for each form can be saved? Thank you "Gary''s Student" wrote: Create a button in the usual way and assign to it the folllowing macro: Sub loxley() n = InputBox("Enter customer name: ") n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub When the button is pressed, the user will be prompted for the customer name. The file will then be save. -- Gary's Student "Robert Loxley" wrote: I am hoping with your programming skills, by way of a coded macro, to save an exel file by operating a button and achieving the following objective automatically... name file as... customer_date_time.xls and save file to specific path for example c:\archive\product A --- please note customer name is variable - which is selected from a drop down list in a specific cell in an order form. date & time is real time The time variable is required and preferable as it is possible for 1 customer to take 2 different products on the same date. One cannot have to two file names the same!. For example : customer A_301006.xls (X2) wont work but customer A_301006_1000.xls and customer A_301006_1005 will save without conflict ---- Background information... I have 3 order forms set out vertically on a single spreadsheet covering 1 specific product. There is more than 1 product! (Product A-Z). All 3 order forms on the Product A spreadsheet are for the same product. All 3 order forms can be used for different customers selected from a drop down list in a specific cell located within the actual order form. The individual orders are only saved to a specific archive product location folder manually at the moment! (example c:\archive\product A) once customer has recieved the product. As all 3 order forms are on 1 spreadsheet I dont want to save all 3 simultaneoulsly, otherwise I will unnecessarily save duplicate orders! and create unnecessarily large files. I would only like to save the respective cells to cover each order form individually ie I will require an individual save button located side on to each order form. The forms are already cleared by way of a simple macro to continually re use. Thank you for reading my request Robert |
product date stamp file save
Thank you and no need to aplolgize!
"Gary''s Student" wrote: I apologize. It is on my "to to" list and I will try to get to it today. Sorry again. -- Gary's Student "Robert Loxley" wrote: Hi Gary Have you had an opprtunity to have a look at the code yet? Thank you Robert Loxley "Gary''s Student" wrote: Sure. I'll take a look a Peterson's code this evening after the Help Center empties out and update this post tomorrow. -- Gary's Student "Robert Loxley" wrote: Gary I found a maro written by Dave Petersen which amongst other functions saves a defined cell range rather than the whole spreadsheet to a specific file. if you visit...will see what I mean http://www.microsoft.com/office/comm...0-80ff66ce0ed5 Would you be able to adapt part of that code to include in your brilliantly written code to save a cell range from (A1 to H40) Thats the top order form. which will be saved to top left hand corner of saved order form remembering that there are 2 other form templates which are further down the spreadsheet. I would appreciate it if you could help again! "Robert Loxley" wrote: Gary It works a treat. I will let you have the rest of the day off! I am going to puzzle out the last part of what I was hoping to achieve with reference to only saving an individual order form rather than all 3 in one go. Thank you again "Gary''s Student" wrote: Sorry, it also failed for me. This works: Sub loxley() Dim n As String Dim r As Range Set r = Cells(1, 1) n = r.Value n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub -- Gary''s Student "Robert Loxley" wrote: Thanks Gary i replaced the macro with your suggestion following message appeared... run time error '1004' method 'range' of object' global failed. first line of code highlighed in yellow color - thats on the line with the cell reference does that mean anything to you? thank you "Gary''s Student" wrote: Lets assume that the customer name is in Cell A1, then: Sub loxley() n = Range(A1).Value n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub You can change the A1 to the proper cell. With regard to your second question, I dont know. -- Gary's Student "Robert Loxley" wrote: Thank you Gary! Is there any way... 1 - that the customer variable which is available from within my order form can be used automatically to create the complete file name rather than the operator completing that part manually? 2 - as there are 3 order forms located vertically per sheet i preferably would only like to save the 1 order for per save function rather than 3. is there any way only the cell range for each form can be saved? Thank you "Gary''s Student" wrote: Create a button in the usual way and assign to it the folllowing macro: Sub loxley() n = InputBox("Enter customer name: ") n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub When the button is pressed, the user will be prompted for the customer name. The file will then be save. -- Gary's Student "Robert Loxley" wrote: I am hoping with your programming skills, by way of a coded macro, to save an exel file by operating a button and achieving the following objective automatically... name file as... customer_date_time.xls and save file to specific path for example c:\archive\product A --- please note customer name is variable - which is selected from a drop down list in a specific cell in an order form. date & time is real time The time variable is required and preferable as it is possible for 1 customer to take 2 different products on the same date. One cannot have to two file names the same!. For example : customer A_301006.xls (X2) wont work but customer A_301006_1000.xls and customer A_301006_1005 will save without conflict ---- Background information... I have 3 order forms set out vertically on a single spreadsheet covering 1 specific product. There is more than 1 product! (Product A-Z). All 3 order forms on the Product A spreadsheet are for the same product. All 3 order forms can be used for different customers selected from a drop down list in a specific cell located within the actual order form. The individual orders are only saved to a specific archive product location folder manually at the moment! (example c:\archive\product A) once customer has recieved the product. As all 3 order forms are on 1 spreadsheet I dont want to save all 3 simultaneoulsly, otherwise I will unnecessarily save duplicate orders! and create unnecessarily large files. I would only like to save the respective cells to cover each order form individually ie I will require an individual save button located side on to each order form. The forms are already cleared by way of a simple macro to continually re use. Thank you for reading my request Robert |
product date stamp file save
Gary
Any luck yet? "Robert Loxley" wrote: Thank you and no need to aplolgize! "Gary''s Student" wrote: I apologize. It is on my "to to" list and I will try to get to it today. Sorry again. -- Gary's Student "Robert Loxley" wrote: Hi Gary Have you had an opprtunity to have a look at the code yet? Thank you Robert Loxley "Gary''s Student" wrote: Sure. I'll take a look a Peterson's code this evening after the Help Center empties out and update this post tomorrow. -- Gary's Student "Robert Loxley" wrote: Gary I found a maro written by Dave Petersen which amongst other functions saves a defined cell range rather than the whole spreadsheet to a specific file. if you visit...will see what I mean http://www.microsoft.com/office/comm...0-80ff66ce0ed5 Would you be able to adapt part of that code to include in your brilliantly written code to save a cell range from (A1 to H40) Thats the top order form. which will be saved to top left hand corner of saved order form remembering that there are 2 other form templates which are further down the spreadsheet. I would appreciate it if you could help again! "Robert Loxley" wrote: Gary It works a treat. I will let you have the rest of the day off! I am going to puzzle out the last part of what I was hoping to achieve with reference to only saving an individual order form rather than all 3 in one go. Thank you again "Gary''s Student" wrote: Sorry, it also failed for me. This works: Sub loxley() Dim n As String Dim r As Range Set r = Cells(1, 1) n = r.Value n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub -- Gary''s Student "Robert Loxley" wrote: Thanks Gary i replaced the macro with your suggestion following message appeared... run time error '1004' method 'range' of object' global failed. first line of code highlighed in yellow color - thats on the line with the cell reference does that mean anything to you? thank you "Gary''s Student" wrote: Lets assume that the customer name is in Cell A1, then: Sub loxley() n = Range(A1).Value n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub You can change the A1 to the proper cell. With regard to your second question, I dont know. -- Gary's Student "Robert Loxley" wrote: Thank you Gary! Is there any way... 1 - that the customer variable which is available from within my order form can be used automatically to create the complete file name rather than the operator completing that part manually? 2 - as there are 3 order forms located vertically per sheet i preferably would only like to save the 1 order for per save function rather than 3. is there any way only the cell range for each form can be saved? Thank you "Gary''s Student" wrote: Create a button in the usual way and assign to it the folllowing macro: Sub loxley() n = InputBox("Enter customer name: ") n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm") ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n End Sub When the button is pressed, the user will be prompted for the customer name. The file will then be save. -- Gary's Student "Robert Loxley" wrote: I am hoping with your programming skills, by way of a coded macro, to save an exel file by operating a button and achieving the following objective automatically... name file as... customer_date_time.xls and save file to specific path for example c:\archive\product A --- please note customer name is variable - which is selected from a drop down list in a specific cell in an order form. date & time is real time The time variable is required and preferable as it is possible for 1 customer to take 2 different products on the same date. One cannot have to two file names the same!. For example : customer A_301006.xls (X2) wont work but customer A_301006_1000.xls and customer A_301006_1005 will save without conflict ---- Background information... I have 3 order forms set out vertically on a single spreadsheet covering 1 specific product. There is more than 1 product! (Product A-Z). All 3 order forms on the Product A spreadsheet are for the same product. All 3 order forms can be used for different customers selected from a drop down list in a specific cell located within the actual order form. The individual orders are only saved to a specific archive product location folder manually at the moment! (example c:\archive\product A) once customer has recieved the product. As all 3 order forms are on 1 spreadsheet I dont want to save all 3 simultaneoulsly, otherwise I will unnecessarily save duplicate orders! and create unnecessarily large files. I would only like to save the respective cells to cover each order form individually ie I will require an individual save button located side on to each order form. The forms are already cleared by way of a simple macro to continually re use. Thank you for reading my request Robert |
| All times are GMT +1. The time now is 10:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com