Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Creating and Naming Tabs

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I create new budget models each year at my company. I create a tab for each account number(hundreds), and it takes tons of time to rename each tab as an account number. Is there a way to create tabs in a workbook from a list, so that I don't have to change the tab name manually for each one?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Man, those workbooks must be BIG !

    About your question, yes, you can do a macro.

    Select your list of names(numbers) and rename it to something, i'll assume SHEETNAMES

    Then, copy and paste this macro in a module of your workbook (Or your personal.xls workbook). Then, run it !

    Code:
    Sub CreateSheets()
    Dim ShtNames As Variant
    Dim i As Long
    
    ShtNames = Range("SHEETNAMES").Value
    Application.ScreenUpdating = False
    For i = LBound(ShtNames) To UBound(ShtNames)
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = ShtNames(i, 1)
    Next i
    Application.ScreenUpdating = True
    End Sub

    _________________
    Regards,

    Juan Pablo G.
    MrExcel.com Consulting

    [ This Message was edited by: Juan Pablo G. on 2002-05-09 15:14 ]

  3. #3
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this! Hope it helps. JSW

    Public myTable
    Sub tableSheets()
    'The "Public myTable" varriable must be the first code in your module.
    'myTable is the row of your tab name in your name table.

    'This code must be run from a module.
    'With macro options add a hot key to run this macro.

    'Sheet1, Row "1" and Column "D" is the starting address of your
    'table of sheet names, you must start in Row "1" you
    'can change the sheet or column in the code below!

    'Every time you hit your hot key, the code will add a new
    'sheet with the next name from your table (like: Ctrl + a).

    myTable = myTable + 1
    Sheets.Add.Name = Worksheets("Sheet1").Range("D" & myTable)

    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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