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

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

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

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

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

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