Add Sheets per Column Cell Contents (VBA)

[nickwilshaw]

Hi Guys,

I need sonme help with VBA code so as to add sheets to a workbook per the contents of a list ( a column in a sheet).

For Example, say sheet "Customers" contains 25 customer names in Column A - eg A,B,C,D etc then I would want 25 sheets to be added and for those sheets to be named A,B,C,D etc.

Thus far, I have been trying to develop:

Sub NameWorksheet()
Range("A2").Select ' cos A1 is the header "Customer"
Do While IsEmpty(Sheets("Customer").Range("A2").Offset(0, 1)) = False
Sheets.Add.Name = ActiveCell.Text
ActiveCell.Offset(1, 0).Select

Loop

End Sub

However, a principal stumbling block is the

Sheets.Add.Name = ActiveCell.Text as VBA want a range, say, ="A2" rather than = ActiveCell.

Equally, I may be approaching this the wrong way and any pointers would again be most appreciated.

Best regards and many thanks
Nick

 

[Sektor]

[COLOR="Blue"]Sub[/COLOR] NameWorksheet()
    [COLOR="Blue"]Dim[/COLOR] i [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR]
    Application.ScreenUpdating = False
    [COLOR="Blue"]With[/COLOR] Sheets("Customer")
        [COLOR="Blue"]For[/COLOR] i = 1 [COLOR="Blue"]To[/COLOR] .Range("A2").End(xlToRight).Column
            Sheets.Add(After:=Sheets(Sheets.Count)).Name = .Cells(2, i)
        [COLOR="Blue"]Next[/COLOR]
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[njimack]

Sub NameWorksheet()
Dim Last_Row As Long
Dim i As Long
Application.ScreenUpdating = False
With Sheets("Customers")
    Last_Row = .Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 2 To Last_Row
        Sheets.Add().Name = .Cells(i, 1).Value
    Next i
End With
Application.ScreenUpdating = True
End Sub

 

[nickwilshaw]

Hi njimack,

Many thanks for that - words a treat!

Just out of curioisity - what is the rationale behind the syntax of the Last_Row statement.

I appreciate that it is trying to count how many rows (have something in them?) but what does the xlup do for example and why does it need to be defined in this way?

Again, many thanks for your help

 

[nickwilshaw]

Hi Sektor,

Thanks for the post but this actually adds sheets named according to the content of Row A whereas I wanted it based on Column A.

Will be a useful post for others as I am sure that there will be those who need to achieve this result as well!

 

[Sektor]

[COLOR="Blue"]Sub[/COLOR] NameWorksheet()
    [COLOR="Blue"]Dim[/COLOR] i [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR]
    [COLOR="Blue"]With[/COLOR] Sheets("Customer")
        [COLOR="Blue"]For[/COLOR] i = 1 [COLOR="Blue"]To[/COLOR] .Range("A2").End(xlDown).Row
            Sheets.Add(After:=Sheets(Sheets.Count)).Name = .Cells(i, 1)
        [COLOR="Blue"]Next[/COLOR]
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[Weaver]

Just out of curioisity - what is the rationale behind the syntax of the Last_Row statement.

I appreciate that it is trying to count how many rows (have something in them?) but what does the xlup do for example and why does it need to be defined in this way?

Select the very last row in your sheet (row 65536 in 2003) in a column with data. Then hold the ctrl key down and press the up arrow. You should land on the first piece of data found in that column, searching from the bottom, up.

In effect, that's what the piece of code is doing.