Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Excel 2007: I need to make tabs automatically and place the tab name in a cell on each new tab. What I have below, from a command button, will make the new tab. How can it read from a list of names and automatically create a tab with the name in cell B3 on each tab?

Private Sub CmdNewTAB_Click()
Worksheets("FocusAreas").Copy After:=Worksheets(Worksheets.Count)
'Active.Sheet.Name = NewSheet.Value
'Tab name in B3
End Sub
share|improve this question
up vote 2 down vote accepted

If your list is a value of items within an excel worksheet, and you want to add tabs based on that list, you COULD do something like this:

Sub Readinto_array()

On Error Resume Next
Dim arrData() As Variant
arrData = Range("D5:D9").Value

For Each cData In arrData
    If cData <> "" Then
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = cData
        Worksheets(cData).Range("B3") = cData

        End If
Next cData
End Sub

Assumptions for the above:

  1. List of names is on same worksheet as the button.
  2. List is on same workbook as the button.

The Worksheets(cData).Range("B3") = cData is the part that adds the worksheet name to the new sheet in cell B3.

If you can specify where your list is located in a simliar manner; if it's not on the same sheet as the button, then specify the proper value in the arrData = Range() portion of the VBA file, such as: arrData = Worksheets("SourceSheet").Range("D5:D9").Value.

share|improve this answer
This is spot on. – mtone Jan 6 '12 at 23:32

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.