Renaming a tab to a date from a cell value (Tom Ogilvy code)

[Guest]

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

 

[Guest]

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

 

[Guest]

Vergel,

Thanks! Works great

Mike

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

 

[Guest]

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

 

[Guest]

Mike,

to append Sheet1 to another workbook:

Dim wb As Workbook
Set wb = Workbooks.Open("S:\common\Production\2007\March.xls")
Sheet1.Copy After:=wb.Sheets(wb.Sheets.Count)
wb.Save
wb.Close

 

[Guest]

Thanks kind sir!

Mike

Mike,

to append Sheet1 to another workbook:

Dim wb As Workbook
Set wb = Workbooks.Open("S:\common\Production\2007\March.xls")
Sheet1.Copy After:=wb.Sheets(wb.Sheets.Count)
wb.Save
wb.Close