Automate Tab Names in a workbook

**krish2503** · 07-09-2015, 11:11 AM

Hi,

Is it possible to have a set of values on one worksheet and have them come up as tab names on following worksheets. For example, on worksheet 1, I have a list of countries viz. UK, France, Germany, Spain and Italy on cells A1:A5. Once I run the macro (I guess it has to be a macro), I need it to check that there are no tabs by that name and then create a blank worksheet with a tab name for the said country (if the country tab already exists, then ignore).
In the above example, I will then end up with 6 tabs with tab names 1, UK, France, Germany, Spain, Italy.

Appreciate your help.

Rgds

Krishna

**Bernie Deitrick** · 07-09-2015, 12:33 PM

Sub MakeSheets()
    Dim c As Range
    Dim strN As String
    
    On Error GoTo CreateSheet
    With Worksheets("1")
        For Each c In .Range(.Range("A1"), .Cells(.Rows.Count, "A").End(xlUp))
            If c.Value <> "" Then strN = Worksheets(c.Value).Name
        Next c
    End With
    Exit Sub
    
CreateSheet:
    Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = c.Value
    Resume
End Sub

**krish2503** · 07-10-2015, 04:01 AM

Thanks, Bernie..works very well. How do we close it off, if we want the macro to return us to tab 1 cell A1?

Really appreciate your help..

Cheers

**Bernie Deitrick** · 07-10-2015, 07:09 AM

Here you go...

Sub MakeSheets()
    Dim c As Range
    Dim strN As String
    
    On Error GoTo CreateSheet
    With Worksheets("1")
        For Each c In .Range(.Range("A1"), .Cells(.Rows.Count, "A").End(xlUp))
            If c.Value <> "" Then strN = Worksheets(c.Value).Name
        Next c
        .Activate
        .Range("A1").Select
    End With

    Exit Sub
    
CreateSheet:
    Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = c.Value
    Resume
End Sub

**krish2503** · 07-10-2015, 08:31 AM

Thanks, Bernie...works great.

Automate Tab Names in a workbook

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Automate Tab Names in a workbook

Re: Automate Tab Names in a workbook

Re: Automate Tab Names in a workbook

Re: Automate Tab Names in a workbook

Re: Automate Tab Names in a workbook

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Copy Values From Each Workbook in Folder to a Single Sheet in New Workbook +Workbook names

Automate new sheets in workbook

Automate new workbook

automate copy and paste whole workbook to new workbook

Automate defining names

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions