Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Name Some Sheets by Text From Cell

  1. #1
    Join Date
    25th July 2006
    Posts
    51

    Name Some Sheets by Text From Cell

    I have 20 sheets in the same workbook. I need to name each one by name found in a certain cell. Thank you
    shalom

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Name a Sheet by text from a cell

    Hi,

    VB:
    Dim i   As Byte 
     
    For i = 1 To Sheets.Count 
        Sheets(i).Name = Sheets(i).Range("A1").Value 
    Next 
    
    
    HTH

  3. #3
    Join Date
    25th July 2006
    Posts
    51

    Re: Name Some Sheets by Text From Cell

    Thank you for your reply, yet it looks to me as part of a code. How I adjust and the complete code if you have please. Thanks.

    shalom

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    26th January 2003
    Location
    UK
    Posts
    4,635

    Re: Name Some Sheets by Text From Cell

    Why does this need tobe in VBA - 20 could be easier and more easy to maintain by manual input, would take less that a minute

    The order of sheets is what worries me, You might needs a helper column to states the real sheets numbers and the tab amendment in another

    jiuk

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    25th July 2006
    Posts
    51

    Re: Name Some Sheets by Text From Cell

    Thank you for your reply. I received the file from abroad and didn't plan it myself. To me it looks like there was database in access and reports which were saved as Excel sheets in one workbook. I received for processing only the sheets. Instead of naming each tab (now 20 but soon many more) manually I thought if there is a way to link the tab name to a the same cell in each sheet automatically.
    Thank you.
    shalom

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    29th July 2006
    Posts
    224

    Re: Name Some Sheets by Text From Cell

    Quote Originally Posted by adaleh
    Thank you for your reply. I received the file from abroad and didn't plan it myself. To me it looks like there was database in access and reports which were saved as Excel sheets in one workbook. I received for processing only the sheets. Instead of naming each tab (now 20 but soon many more) manually I thought if there is a way to link the tab name to a the same cell in each sheet automatically.
    Thank you.
    shalom
    As far as i know there is no manual way to do this, you will have to use code (VBA) as in the above example.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    26th January 2003
    Location
    UK
    Posts
    4,635

    Re: Name Some Sheets by Text From Cell

    Ok so then how can we know the running order of the sheets and what names they should take in turn. Else we could be naming any old sheet with any old name <bwer!>

    jiuk

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    25th July 2006
    Posts
    51

    Re: Name Some Sheets by Text From Cell

    The VBA code of Krishnakumar did it! Thank you so much. Here is the final code:
    VB:
    Sub nameit() 
         
         
        Dim i   As Byte 
         
        For i = 1 To Sheets.Count 
            Sheets(i).Name = Sheets(i).Range("A1").Value 
        Next 
    End Sub 
    
    
    Thank you very much.

    shalom

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 2
    Last Post: May 9th, 2008, 01:14
  2. Paste Text To Many Sheets
    By mikesafe in forum Excel General
    Replies: 4
    Last Post: February 9th, 2007, 00:18
  3. Add Sheets & Name As Cell Text
    By Peter J QLD in forum Excel General
    Replies: 7
    Last Post: December 24th, 2006, 09:39
  4. Formatting All sheets to Text
    By ryans in forum Excel General
    Replies: 2
    Last Post: November 19th, 2004, 15:22
  5. text to columns on multiple sheets
    By opeyemi1 in forum Excel General
    Replies: 2
    Last Post: November 4th, 2004, 11:19

Bookmarks

Posting Permissions

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