Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add sheet and rename with cell contents
Here's what I'm trying to do:
We pull some server data using a VBS script, which is then placed in a .csv file. To make things easier on the non-excel users, I want ot automate putting the data in the workbook. So my goal is to have a macro that they run that does the following: Inserts new sheet Imports data from the file Adds a column Creates a new formula and pastes it down through the column Defines a few names in the sheet Places the current date in a cell Date is replaced as a value so it doesn't change Use date value as sheet name. I'm good to go all the way down to the last part "Use date value as sheet name". So if the date value ends up in J1, how can I get that value as the sheet name? Thanks a bunch, Rudy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add sheet and rename with cell contents
Rudy
Use the date format that Matches your needs ActiveSheet.Name = Format(Range("J1").Value, "YYMMDD") or ActiveSheet.Name = "Abc " & Format(Range("J1").Value, "YYMMDD") --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add sheet and rename with cell contents
It seems that you can name the sheet according to date and not to some cell
content - see below We pull some server data using a VBS script, which is then placed in a .csv file. To make things easier on the non-excel users, I want ot automate putting the data in the workbook. So my goal is to have a macro that they run that does the following: Inserts new sheet Imports data from the file Adds a column Creates a new formula and pastes it down through the column Defines a few names in the sheet Places the current date in a cell 'so you know it's a date, you know its value...so ActiveSheet.Name = Left(Date,10) ' or other format as Mudraker points out Date is replaced as a value so it doesn't change [cut Use date value as sheet name.] I'm good to go all the way down to the last part "Use date value as sheet name". So if the date value ends up in J1, how can I get that value as the sheet name? Thanks a bunch, Rudy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add sheet and rename with cell contents
Hi Rudy,
For sheet names, you must avoid certain characters like / and , (comma). So you must format the date correctly. ActiveSheet.Name = Format(Range("A1").Value, "dd mmm yyyy") Note that formatting the date in the cell and then using ActiveSheet.Name = Range("A1").Value will not work as it will lift the date value in mm/dd/yyyy format (it did on my machine), which then generates an error. regards Paul "Rudy Woltner" wrote in message ... Here's what I'm trying to do: We pull some server data using a VBS script, which is then placed in a .csv file. To make things easier on the non-excel users, I want ot automate putting the data in the workbook. So my goal is to have a macro that they run that does the following: Inserts new sheet Imports data from the file Adds a column Creates a new formula and pastes it down through the column Defines a few names in the sheet Places the current date in a cell Date is replaced as a value so it doesn't change Use date value as sheet name. I'm good to go all the way down to the last part "Use date value as sheet name". So if the date value ends up in J1, how can I get that value as the sheet name? Thanks a bunch, Rudy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
THANKS
THANKS for the quick responses. I had tried several
combinations that were very similar to your solutions. Now I can finish this projet today. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rename tab to cell contents | Excel Worksheet Functions | |||
rename worksheet based on contents of a cell in different workshee | Excel Discussion (Misc queries) | |||
Rename Multiple Excel Workbooks based on cell contents | Excel Discussion (Misc queries) | |||
new worksheet and rename from cell contents macro | Excel Worksheet Functions | |||
How do I automatically rename a sheet with the contents of a cell. | Excel Discussion (Misc queries) |