Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

1 Answer

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
add comment

Your Answer

 
discard

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.