If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Saving a worksheet to a name from a cell
Hi,
I want to have the user enter a date and then reformat this to a series of digits and use this as the workbook name. So, I have the3 user enter the date and then strip out the MM, DD and YY stuff after I enter the data and place this into another cell. I want a macro to then fire that does a save as operation using the value in the cell. For example, user enter 12/06/03 into cell A1. Cel A2 reformats this as 120603MGA (the MGA are user ID characters that also get added) and then an operation fires to save the workbook as 120603MGA.xls Any ideas? I recorded a macro to capture main functyions but as a newbie, I am not quite sure how to capture the value in cell A2 and stuff it into the filename. Can someone point me to the object model and function calls for the various worksheet objects. TIA. Mick |
#2
|
|||
|
|||
Saving a worksheet to a name from a cell
Mick,
Not sure where the usrer id comes from but the date can be formatted format(range("A1"),"ddmmyy") so if a variable userid holds the user id, the save statement would be something aklin to ThisWorkbook.SaveAs Filename = Format(Range("A1"), "ddmmyy") & UserId & ".xls" -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Mick Garrett" wrote in message ... Hi, I want to have the user enter a date and then reformat this to a series of digits and use this as the workbook name. So, I have the3 user enter the date and then strip out the MM, DD and YY stuff after I enter the data and place this into another cell. I want a macro to then fire that does a save as operation using the value in the cell. For example, user enter 12/06/03 into cell A1. Cel A2 reformats this as 120603MGA (the MGA are user ID characters that also get added) and then an operation fires to save the workbook as 120603MGA.xls Any ideas? I recorded a macro to capture main functyions but as a newbie, I am not quite sure how to capture the value in cell A2 and stuff it into the filename. Can someone point me to the object model and function calls for the various worksheet objects. TIA. Mick |
#3
|
|||
|
|||
Saving a worksheet to a name from a cell
Thanks Bob,
Is there any way to have this automatically fire when you leave the date cell, rather than having to manually run it using a button click event? TIA. Mick -----Original Message----- Mick, Not sure where the usrer id comes from but the date can be formatted format(range("A1"),"ddmmyy") so if a variable userid holds the user id, the save statement would be something aklin to ThisWorkbook.SaveAs Filename = Format(Range ("A1"), "ddmmyy") & UserId & ".xls" -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Mick Garrett" wrote in message ... Hi, I want to have the user enter a date and then reformat this to a series of digits and use this as the workbook name. So, I have the3 user enter the date and then strip out the MM, DD and YY stuff after I enter the data and place this into another cell. I want a macro to then fire that does a save as operation using the value in the cell. For example, user enter 12/06/03 into cell A1. Cel A2 reformats this as 120603MGA (the MGA are user ID characters that also get added) and then an operation fires to save the workbook as 120603MGA.xls Any ideas? I recorded a macro to capture main functyions but as a newbie, I am not quite sure how to capture the value in cell A2 and stuff it into the filename. Can someone point me to the object model and function calls for the various worksheet objects. TIA. Mick . |
#4
|
|||
|
|||
Saving a worksheet to a name from a cell
Hi Mick,
You could get to do this using worksheet event code. Something like this Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = True On Error GoTo ws_exit If Not Intersect(Target, Range("A1")) Is Nothing Then If IsDate(PrevCell.Value) Then ThisWorkbook.SaveAs Filename:= _ Format(Range("A1").Value, "ddmmyy") & Range("B1").Value & ".xls" End If End If ws_exit: Application.EnableEvents = True End Sub This will save it when you change the cell A1 and put a valid date in it. I have assumed that B1 holds the user id. You will need to change to suit. As worksheet event code, it goes in the worksheet module. Righr-click on the sheet tab, select View code fropm the menu, and paste the code in there. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Mick Garrett" wrote in message ... Thanks Bob, Is there any way to have this automatically fire when you leave the date cell, rather than having to manually run it using a button click event? TIA. Mick -----Original Message----- Mick, Not sure where the usrer id comes from but the date can be formatted format(range("A1"),"ddmmyy") so if a variable userid holds the user id, the save statement would be something aklin to ThisWorkbook.SaveAs Filename = Format(Range ("A1"), "ddmmyy") & UserId & ".xls" -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Mick Garrett" wrote in message ... Hi, I want to have the user enter a date and then reformat this to a series of digits and use this as the workbook name. So, I have the3 user enter the date and then strip out the MM, DD and YY stuff after I enter the data and place this into another cell. I want a macro to then fire that does a save as operation using the value in the cell. For example, user enter 12/06/03 into cell A1. Cel A2 reformats this as 120603MGA (the MGA are user ID characters that also get added) and then an operation fires to save the workbook as 120603MGA.xls Any ideas? I recorded a macro to capture main functyions but as a newbie, I am not quite sure how to capture the value in cell A2 and stuff it into the filename. Can someone point me to the object model and function calls for the various worksheet objects. TIA. Mick . |
#5
|
|||
|
|||
Saving a worksheet to a name from a cell
You are a wiz - thanks.
-----Original Message----- Hi Mick, You could get to do this using worksheet event code. Something like this Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = True On Error GoTo ws_exit If Not Intersect(Target, Range("A1")) Is Nothing Then If IsDate(PrevCell.Value) Then ThisWorkbook.SaveAs Filename:= _ Format(Range("A1").Value, "ddmmyy") & Range("B1").Value & ".xls" End If End If ws_exit: Application.EnableEvents = True End Sub This will save it when you change the cell A1 and put a valid date in it. I have assumed that B1 holds the user id. You will need to change to suit. As worksheet event code, it goes in the worksheet module. Righr-click on the sheet tab, select View code fropm the menu, and paste the code in there. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Mick Garrett" wrote in message ... Thanks Bob, Is there any way to have this automatically fire when you leave the date cell, rather than having to manually run it using a button click event? TIA. Mick -----Original Message----- Mick, Not sure where the usrer id comes from but the date can be formatted format(range("A1"),"ddmmyy") so if a variable userid holds the user id, the save statement would be something aklin to ThisWorkbook.SaveAs Filename = Format(Range ("A1"), "ddmmyy") & UserId & ".xls" -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Mick Garrett" wrote in message ... Hi, I want to have the user enter a date and then reformat this to a series of digits and use this as the workbook name. So, I have the3 user enter the date and then strip out the MM, DD and YY stuff after I enter the data and place this into another cell. I want a macro to then fire that does a save as operation using the value in the cell. For example, user enter 12/06/03 into cell A1. Cel A2 reformats this as 120603MGA (the MGA are user ID characters that also get added) and then an operation fires to save the workbook as 120603MGA.xls Any ideas? I recorded a macro to capture main functyions but as a newbie, I am not quite sure how to capture the value in cell A2 and stuff it into the filename. Can someone point me to the object model and function calls for the various worksheet objects. TIA. Mick . . |
Thread Tools | |
Display Modes | |
|
|