Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Mar 2004
    Location
    Albany, New York, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Tab Name Change (2003)

    Hello All,

    I read a post on having the name of a tab automatically change depending on a target cell. I tried to modify that info to fit what I need without success. I have a macro that copies data from a workbook, then open a saved workbook, create a new worksheet, paste the data save then close. I would like it to automatically rename the tab it creates with date it's pasted. My macro already pastes the date in cell A1 on the new tab, so if I need a target, I have one.

    any thoughts?

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

    Re: Tab Name Change (2003)

    What code do you have, and where does it fail?

  3. #3
    Lounger
    Join Date
    Mar 2004
    Location
    Albany, New York, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab Name Change (2003)

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
    ActiveSheet.Name = Range("A1")
    End If
    End Sub


    If fails because everytime I make a new tab the formula can't find the reference for the new tab...just the old. Would it be possible to make the formula change the name on the new tab or current active sheet upon closing? would that be easier?

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

    Re: Tab Name Change (2003)

    If you create the new worksheet in code, you can set its name at the same time. this would be the easiest solution.

    If you rename it afterwards, you'd have to loop through all worksheets and rename those that you want to rename.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Tab Name Change (2003)

    <P ID="edit" class=small>(Edited by sdckapr on 24-Jul-07 20:02. Added PS)</P>If you want the code to do it for all sheets in the workbook you can add this code to the thisworkbook object:

    <pre>Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    If Not Intersect(Target, Sh.Range("A1")) Is Nothing Then
    Sh.Name = Sh.Range("A1")
    End If
    End Sub</pre>


    Then it will work will all current sheets and all new sheets

    Steve
    PS it probably should have some error checking to avoid runtime errors with invalid names, but I leave that to you...

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

    Re: Tab Name Change (2003)

    Since worksheets must have names, and since names can not be duplicated, and since some characters can not be used in names, I think some addition error checking and recovery should be added to this type of code:

    <code>
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    If Not Intersect(Target, Sh.Range("A1")) Is Nothing Then
    If Sh.Range("A1") <> "" Then
    On Error Resume Next
    Sh.Name = Sh.Range("A1")
    On Error GoTo 0
    End If
    End If
    End Sub
    </code>
    Legare Coleman

Posting Permissions

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