adding tabs and naming them automatically

T

timmulla

Can anyone help me with code that can automatically creates a new tab with
the click of a commandButton.

I would like the code to loop through column A (starting in A2) and when it
finds a letter "X" in a row, it will automatically create a new tab and name
it with the accompanying text in column C.

Any help would be appreciated.
 
D

Doug Glancy

timmulla,

Here's some code to get you started at least:

Sub add_sheets
Dim last_row As Long
Dim cell As Range

With ActiveSheet
last_row = .Range("A" & Rows.Count).End(xlUp).Row
For Each cell In .Range("A2:A" & last_row)
If UCase(cell.Value) = "X" Then
ThisWorkbook.Worksheets.Add
after:=Worksheets(ThisWorkbook.Worksheets.Count)
ActiveSheet.Name = .Range("C" & cell.Row).Value
End If
Next cell
End With
End Sub


hth,

Doug
 
J

Jimbo213

Doug - that's pretty clever

How can I add worksheet tabs where the names are in B1 to (last value in Row
1)

--
Thanks for your reply & assistance.
Jimbo213


Doug Glancy said:
timmulla,

Here's some code to get you started at least:

Sub add_sheets
Dim last_row As Long
Dim cell As Range

With ActiveSheet
last_row = .Range("A" & Rows.Count).End(xlUp).Row
For Each cell In .Range("A2:A" & last_row)
If UCase(cell.Value) = "X" Then
ThisWorkbook.Worksheets.Add
after:=Worksheets(ThisWorkbook.Worksheets.Count)
ActiveSheet.Name = .Range("C" & cell.Row).Value
End If
Next cell
End With
End Sub


hth,

Doug
Click to expand...
 
S

stanleydgromjr

Jimbo213,

Try:


Code
-------------------


Option Explicit
Sub add_sheets()
Dim c As Range
With ActiveSheet
For Each c In .Range("B1", .Range("B" & .Rows.Count).End(xlUp))
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = c.Value
Next c
End With
End Sub


-------------------



Have a great day,
Sta
 
J

Jimbo213

The values I want for tab names are in row 1, not column B.
I want to create a tab for with the values in B1, C1, D1, E1, .... X1 [for
example]

Will your proposed code do that?
 
S

stanleydgromjr

Jimbo213,

Try:


Code
-------------------


Option Explicit
Sub add_sheets()
Dim c As Range
Dim LC As Long
With ActiveSheet
LC = .Cells(1, Columns.Count).End(xlToLeft).Column
For Each c In .Range(.Cells(1, 2), .Cells(1, LC))
If c <> "" Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = c.Value
End If
Next c
End With
End Sub


-------------------



Have a great day,
Sta
 
Top