Need to use a macro to retitle an Excel worksheet

Lydokane · 11-19-2008, 11:46 AM

I am trying to make a macro in MS Excel that renames a worksheet using data from a specific cell in that same worksheet.

Here’s the problem. I have a workbook that has a worksheet titled: ROOT DIAMETER. We only collect 150 parts per worksheet. When we get the 150 parts we move that worksheet to an archive workbook. Since we can’t have several worksheets in the same [archive] workbook titled ROOT DIAMETER, we change the name to: ROOT DIAMETER DATE CREATED.

The date the worksheet was created is also stored in cell B6. I would like to create a macro that automatically renames the worksheet from: ROOT DIAMETER to: ROOT DIAMETER DATE (from cell B6).

Since I am a Visual Basic idiot, I need help with the syntax. Can somebody help?

Thanks,

Lydokane

David M58 · 11-19-2008, 04:41 PM

The following example may be helpful. Suppose you have a worksheet titled ROOT DIAMETER, and on that worksheet, cell B6 contains the date 11/19/2008. The following code will change the name of the worksheet to ROOT DIAMETER 11-19-2008.

Code:

Sub rename_sheet()
    Dim datetext As String
    datetext = Format(Worksheets("ROOT DIAMETER").Range("B6").Value, "mm-dd-yyyy")
    Worksheets("ROOT DIAMETER").Name = "ROOT DIAMETER " & datetext
End Sub

Modify as necessary.

Lydokane · 11-20-2008, 11:16 AM

Quote:

Originally Posted by David M58

The following example may be helpful. Suppose you have a worksheet titled ROOT DIAMETER, and on that worksheet, cell B6 contains the date 11/19/2008. The following code will change the name of the worksheet to ROOT DIAMETER 11-19-2008.

Code:

Sub rename_sheet()
    Dim datetext As String
    datetext = Format(Worksheets("ROOT DIAMETER").Range("B6").Value, "mm-dd-yyyy")
    Worksheets("ROOT DIAMETER").Name = "ROOT DIAMETER " & datetext
End Sub

Modify as necessary.

I'll give it a try.

Thanks.

Lydokane

martes · 11-23-2008, 04:24 PM

You can also use the text property directly

Code:

    With Worksheets("ROOT DIAMETER")
        .Name = "ROOT DIAMETER " & .Range("B6").Text
    End With
End Sub

David M58 · 11-23-2008, 07:31 PM

Martes, your suggestion will work fine if .Range("B6").Text does not contain the "/" character, otherwise it will cause an error because Excel does not allow the "/" character in a sheet name.

11-19-2008, 11:46 AM	#1 (permalink)
Lydokane Tech, Microsoft Support Join Date: May 2005 Location: Michigan City Posts: 480 OS: Win XP Pro SP2	Need to use a macro to retitle an Excel worksheet I am trying to make a macro in MS Excel that renames a worksheet using data from a specific cell in that same worksheet. Here’s the problem. I have a workbook that has a worksheet titled: ROOT DIAMETER. We only collect 150 parts per worksheet. When we get the 150 parts we move that worksheet to an archive workbook. Since we can’t have several worksheets in the same [archive] workbook titled ROOT DIAMETER, we change the name to: ROOT DIAMETER DATE CREATED. The date the worksheet was created is also stored in cell B6. I would like to create a macro that automatically renames the worksheet from: ROOT DIAMETER to: ROOT DIAMETER DATE (from cell B6). Since I am a Visual Basic idiot, I need help with the syntax. Can somebody help? Thanks, Lydokane __________________

11-19-2008, 04:41 PM	#2 (permalink)
David M58 Registered User Join Date: May 2008 Location: Baltimore, Maryland Posts: 160 OS: Windows XP SP3	Re: Need to use a macro to retitle an Excel worksheet The following example may be helpful. Suppose you have a worksheet titled ROOT DIAMETER, and on that worksheet, cell B6 contains the date 11/19/2008. The following code will change the name of the worksheet to ROOT DIAMETER 11-19-2008. Code: Sub rename_sheet() Dim datetext As String datetext = Format(Worksheets("ROOT DIAMETER").Range("B6").Value, "mm-dd-yyyy") Worksheets("ROOT DIAMETER").Name = "ROOT DIAMETER " & datetext End Sub Modify as necessary.

11-23-2008, 04:24 PM	#4 (permalink)
martes Registered User Join Date: Aug 2008 Posts: 21 OS: XP Pro SP2	Re: Need to use a macro to retitle an Excel worksheet You can also use the text property directly Code: With Worksheets("ROOT DIAMETER") .Name = "ROOT DIAMETER " & .Range("B6").Text End With End Sub Last edited by martes; 11-23-2008 at 04:27 PM.

11-23-2008, 07:31 PM	#5 (permalink)
David M58 Registered User Join Date: May 2008 Location: Baltimore, Maryland Posts: 160 OS: Windows XP SP3	Re: Need to use a macro to retitle an Excel worksheet Martes, your suggestion will work fine if .Range("B6").Text does not contain the "/" character, otherwise it will cause an error because Excel does not allow the "/" character in a sheet name.