Excel auto worksheet title function

gwoolley0302

Board Regular
Joined
May 13, 2014
Messages
51
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.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,291
Office Version
  1. 365
Platform
  1. Windows
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.....

Code:
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

Board Regular
Joined
May 13, 2014
Messages
51
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

Well-known Member
Joined
Mar 13, 2006
Messages
1,291
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

No problem,

Just add these two lines before the Next rCountry line
Code:
.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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,131
Messages
5,527,023
Members
409,736
Latest member
maanbunty

This Week's Hot Topics

Top