Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .