Excel auto worksheet title function

[gwoolley0302]

Hi everyone, I'm looking to automate the process of repeating an excel worksheet (which is set up with various tables and formulas) for various countries. I have a referenced list of countries set up in worksheet 1. So for example: in worksheet 2 I have a sheet set up for Albania - and I want to repeat this for another 100 countries. . Can I use the reference list of countries to auto-repeat worksheet 2 for worksheets 3 - 102 and auto-title the worksheet tabs? (E.g. Brazil, Canada, Chile..............) Really appreciate your help.

 

[Darren Bartrup]

Providing there's no illegal characters in the country names and all sheets are Worksheets (normal sheets - not chart sheets or anything else) and your original sheet is called 'Template':

.... edit.... and there's no duplicate country names or blank cells in the country list.....

Public Sub Test()

    Dim rCountryRange As Range
    Dim rCountry As Range
    Dim wrkSht As Worksheet
    
    With ThisWorkbook 'ThisWorkbook is the Excel file that this code is in.
    
        Set rCountryRange = .Worksheets("Sheet1").Range("A1:A6") 'Extend the range to cover all countries.
        
        For Each rCountry In rCountryRange
            .Worksheets("Template").Copy After:=Sheets(.Worksheets.Count)
            .Worksheets(.Worksheets.Count).Name = rCountry.Value
        Next rCountry
    
    End With


End Sub

 

[gwoolley0302]

Thanks Darren - that's great

 

[gwoolley0302]

Hi Darren - sorry complete Macro novice here. If I also wanted to populate every copied worksheet with the respective country name in cells A2:A10 - what would I insert above and whereabouts? Greg

 

[Darren Bartrup]

No problem,

Just add these two lines before the Next rCountry line

.Worksheets(.Worksheets.Count).Range("A2:A10").FormulaArray = rCountry.Value
.Worksheets(.Worksheets.Count).Columns(1).AutoFit

The second line isn't really necessary, but makes sure you can read the country name.

 

[gwoolley0302]

Perfect thanks Darren.

 

[Darren Bartrup]

Glad to help.