Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can I rename a sheet tab based on cell info? (Excel 2000, SP-3)

    I have an Excel spreadsheet where names of staff are entered at the top of columns, and then entries made below on a daily basis. It's like a database table except the staff names change depending on who quits, who is hired, whatever. I need to accumulate the daily inputs into a summary workbook, and I would like to create a tab for each employee. Can I grab the employee names from the cells in workbook #1, and use that information to rename the tabs in workbook #2? I have a macro in place to pull the totals for all employees from one workbook to the other, but we'd like to display comparative information by staff as well. I need to use VBA to do this.

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Can I rename a sheet tab based on cell info? (Excel 2000, SP-3)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> jmrutledge

    Yes...

    You can use VBA to do this, and here are two simple examples.

    Sub RenameMyWorksheets()
    Dim lCounter As Long '/Helps with For-Next Loops

    For lCounter = 1 To 255
    Sheets(lCounter).Name = Range("A" & lCounter).Value
    Next lCounter
    End Sub

    Sub RenameMyWorksheetsInOtherWorkbook()
    Dim lBookCounter As Long '/Hleps in For-Next Loops.
    Dim lCounter As Long '/Helps with For-Next Loops.

    For lBookCounter = 1 To Workbooks.Count
    For lCounter = 1 To 255
    Workbooks(lBookCounter).Sheets(lCounter).Name = Range("A" & lCounter).Value
    Next lCounter
    Next lBookCounter
    End Sub

    Now this is so simple I did not add any tests to make sure that you won't get to worksheets with the same name, if you have two Bills or Johns for example. If you have any long names, then you need to trim it to 31 characters.

    Also this assumes that the names are in Row one.

    This is a starting point, so adjust the code to suite your needs.

    HTH

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Lounger
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can I rename a sheet tab based on cell info? (Excel 2000, SP-3)

    I'm getting a Run-time error 1004 "Application-defined or object-defined error" when I run your code to change the sheet tab names of the current workbook. In debug mode, it stops at this line - Sheets(lCounter).Name = Range("A" & lCounter).Value. I just copied & pasted and ran it.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can I rename a sheet tab based on cell info? (Excel 2000, SP-3)

    Wassim's code contains

    For lCounter = 1 To 255

    twice. This will attempt to process 255 worksheets. Replace 255 by the appropriate number.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can I rename a sheet tab based on cell info? (Excel 2000, SP-3)

    The macro below will create a new workbook and insert a new sheet into it for each name in row 1 of the active workbook and name those sheets with the names in row 1. If there are duplicate names it will append a (2) or (3) etc. to create a unique sheet name. Finally it asks you for a file name to save the new file.

    <pre>Public Sub CreateSummaryWB()
    Dim oDetWB As Workbook, oSumWB As Workbook, oWS As Worksheet
    Dim lLastCol As Long, I As Long, J As Long, lSINWB As Long
    Dim vFileName As Variant
    Dim strName As String
    Application.ScreenUpdating = False
    Set oDetWB = ActiveWorkbook
    lSINWB = Application.SheetsInNewWorkbook
    Application.SheetsInNewWorkbook = 1
    Set oSumWB = Workbooks.Add
    Application.SheetsInNewWorkbook = lSINWB
    oDetWB.Activate
    lLastCol = ActiveSheet.Range("IV1").End(xlToLeft).Column - 1
    For I = 0 To lLastCol
    strName = Range("A1").Offset(0, I).Value
    Set oWS = Nothing
    On Error Resume Next
    Set oWS = oSumWB.Worksheets(strName)
    On Error GoTo 0
    If Not oWS Is Nothing Then
    For J = 2 To 255
    strName = Range("A1").Offset(0, I).Value & " (" & J & ")"
    Set oWS = Nothing
    On Error Resume Next
    Set oWS = oSumWB.Worksheets(strName)
    On Error GoTo 0
    If oWS Is Nothing Then Exit For
    Next J
    If J > 255 Then
    MsgBox "Too many duplicate names."
    Exit Sub
    End If
    End If
    Set oWS = Nothing
    Set oWS = oSumWB.Sheets.Add(Before:=oSumWB.Worksheets(oSumWB .Worksheets.Count))
    oWS.Name = strName
    Next I
    Application.DisplayAlerts = False
    oSumWB.Worksheets(oSumWB.Worksheets.Count).Delete
    oDetWB.Worksheets(1).Activate
    Application.DisplayAlerts = True
    vFileName = Application.GetSaveAsFilename("Summary.xls", "Excel Files (*.xls), *.xls, All Files (*.*), *.*")
    If vFileName = False Then Exit Sub
    oSumWB.SaveAs vFileName
    oSumWB.Activate
    Application.ScreenUpdating = True
    End Sub
    </pre>

    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Palmyra, Virginia, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can I rename a sheet tab based on cell info? (

    This place is such a wonderful resource. This was exactly what I needed. Praise God they finally got the search function working again.

    -----Paul-----

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •