| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming a tab to a date from a cell value (Tom Ogilvy code)
Oh Wise Ones,
I have some code below I got from a previous post of Toms: Sub nosheet() Sheets(Sheets.Count).Name = Replace(Sheets("Sheet1").Range("B16").Text,"/","_") End Sub Which I tried to modify as such (the sheet is named "default") Sub nosheet() Sheets("default").Name = Replace(Sheets("default").Range("B16").Text,"/","_") End Sub also tried Sheets("default").Name =Range("B16").Text,"/","_") Well! it will have no part of that. The whole macro is red fonted in the editor basically I have a date in cell B16 formated as 03/07/07 and I want to rename the tab as 03_07_07 (I know I can't have "/") but I cant get it to reformat. Any help is appreciated Mike |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Renaming a tab to a date from a cell value (Tom Ogilvy code)
Mike,
You just mised the call to Replace function. Try it this way: Sheets("default").Name = Replace(Range("B16").Text,"/","_") Or better, to make sure the Range object is qualified: With Sheets("default") .Name = Replace(.Range("B16").Text, "/", "_") End With "Mike K" wrote: Oh Wise Ones, I have some code below I got from a previous post of Toms: Sub nosheet() Sheets(Sheets.Count).Name = Replace(Sheets("Sheet1").Range("B16").Text,"/","_") End Sub Which I tried to modify as such (the sheet is named "default") Sub nosheet() Sheets("default").Name = Replace(Sheets("default").Range("B16").Text,"/","_") End Sub also tried Sheets("default").Name =Range("B16").Text,"/","_") Well! it will have no part of that. The whole macro is red fonted in the editor basically I have a date in cell B16 formated as 03/07/07 and I want to rename the tab as 03_07_07 (I know I can't have "/") but I cant get it to reformat. Any help is appreciated Mike |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Renaming a tab to a date from a cell value (Tom Ogilvy code)
Vergel,
Thanks! Works great Mike "Vergel Adriano" wrote: Mike, You just mised the call to Replace function. Try it this way: Sheets("default").Name = Replace(Range("B16").Text,"/","_") Or better, to make sure the Range object is qualified: With Sheets("default") .Name = Replace(.Range("B16").Text, "/", "_") End With "Mike K" wrote: Oh Wise Ones, I have some code below I got from a previous post of Toms: Sub nosheet() Sheets(Sheets.Count).Name = Replace(Sheets("Sheet1").Range("B16").Text,"/","_") End Sub Which I tried to modify as such (the sheet is named "default") Sub nosheet() Sheets("default").Name = Replace(Sheets("default").Range("B16").Text,"/","_") End Sub also tried Sheets("default").Name =Range("B16").Text,"/","_") Well! it will have no part of that. The whole macro is red fonted in the editor basically I have a date in cell B16 formated as 03/07/07 and I want to rename the tab as 03_07_07 (I know I can't have "/") but I cant get it to reformat. Any help is appreciated Mike |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Renaming a tab to a date from a cell value (Tom Ogilvy code)
Vergel,
How could I copy that worksheet with the new sheet name and add it to another workbook in another location? The other workbook would increment each day with the new sheet tab added. The original is not kept. S:\common\Production\2007\March.xls Thanks, Mike "Vergel Adriano" wrote: Mike, You just mised the call to Replace function. Try it this way: Sheets("default").Name = Replace(Range("B16").Text,"/","_") Or better, to make sure the Range object is qualified: With Sheets("default") .Name = Replace(.Range("B16").Text, "/", "_") End With "Mike K" wrote: Oh Wise Ones, I have some code below I got from a previous post of Toms: Sub nosheet() Sheets(Sheets.Count).Name = Replace(Sheets("Sheet1").Range("B16").Text,"/","_") End Sub Which I tried to modify as such (the sheet is named "default") Sub nosheet() Sheets("default").Name = Replace(Sheets("default").Range("B16").Text,"/","_") End Sub also tried Sheets("default").Name =Range("B16").Text,"/","_") Well! it will have no part of that. The whole macro is red fonted in the editor basically I have a date in cell B16 formated as 03/07/07 and I want to rename the tab as 03_07_07 (I know I can't have "/") but I cant get it to reformat. Any help is appreciated Mike |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Renaming a tab to a date from a cell value (Tom Ogilvy code)
Mike,
to append Sheet1 to another workbook: Dim wb As Workbook Set wb = Workbooks.Open("S:\common\Production\2007\March.xl s") Sheet1.Copy After:=wb.Sheets(wb.Sheets.Count) wb.Save wb.Close "Mike K" wrote: Vergel, How could I copy that worksheet with the new sheet name and add it to another workbook in another location? The other workbook would increment each day with the new sheet tab added. The original is not kept. S:\common\Production\2007\March.xls Thanks, Mike "Vergel Adriano" wrote: Mike, You just mised the call to Replace function. Try it this way: Sheets("default").Name = Replace(Range("B16").Text,"/","_") Or better, to make sure the Range object is qualified: With Sheets("default") .Name = Replace(.Range("B16").Text, "/", "_") End With "Mike K" wrote: Oh Wise Ones, I have some code below I got from a previous post of Toms: Sub nosheet() Sheets(Sheets.Count).Name = Replace(Sheets("Sheet1").Range("B16").Text,"/","_") End Sub Which I tried to modify as such (the sheet is named "default") Sub nosheet() Sheets("default").Name = Replace(Sheets("default").Range("B16").Text,"/","_") End Sub also tried Sheets("default").Name =Range("B16").Text,"/","_") Well! it will have no part of that. The whole macro is red fonted in the editor basically I have a date in cell B16 formated as 03/07/07 and I want to rename the tab as 03_07_07 (I know I can't have "/") but I cant get it to reformat. Any help is appreciated Mike |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Renaming a tab to a date from a cell value (Tom Ogilvy code)
Thanks kind sir!
Mike "Vergel Adriano" wrote: Mike, to append Sheet1 to another workbook: Dim wb As Workbook Set wb = Workbooks.Open("S:\common\Production\2007\March.xl s") Sheet1.Copy After:=wb.Sheets(wb.Sheets.Count) wb.Save wb.Close "Mike K" wrote: Vergel, How could I copy that worksheet with the new sheet name and add it to another workbook in another location? The other workbook would increment each day with the new sheet tab added. The original is not kept. S:\common\Production\2007\March.xls Thanks, Mike "Vergel Adriano" wrote: Mike, You just mised the call to Replace function. Try it this way: Sheets("default").Name = Replace(Range("B16").Text,"/","_") Or better, to make sure the Range object is qualified: With Sheets("default") .Name = Replace(.Range("B16").Text, "/", "_") End With "Mike K" wrote: Oh Wise Ones, I have some code below I got from a previous post of Toms: Sub nosheet() Sheets(Sheets.Count).Name = Replace(Sheets("Sheet1").Range("B16").Text,"/","_") End Sub Which I tried to modify as such (the sheet is named "default") Sub nosheet() Sheets("default").Name = Replace(Sheets("default").Range("B16").Text,"/","_") End Sub also tried Sheets("default").Name =Range("B16").Text,"/","_") Well! it will have no part of that. The whole macro is red fonted in the editor basically I have a date in cell B16 formated as 03/07/07 and I want to rename the tab as 03_07_07 (I know I can't have "/") but I cant get it to reformat. Any help is appreciated Mike |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Renaming tab name-# of code lines..HELP | Excel Programming | |||
| vb code for renaming a work sheet with a cell reference | Excel Discussion (Misc queries) | |||
| Tom Ogilvy, your code about locating a specified shape in VBA | Excel Programming | |||
| Open Folder - Ogilvy code | Excel Programming | |||
| Add-In / Tool / VBA Code for Renaming Range Names | Excel Programming | |||
Linear Mode
