Creating worksheets with cell values as names
 

Hello

From a group of cells in a worksheet I want to create
worksheets in the same workbook, each of which has the
name of the contents of the cells (all strings).

for example:
value in cell A1 is 'Ball Valve'
valve in cell A2 is 'Globe Valve'

I want to create 2 worksheets: one named 'Ball Valve', the
other 'Globe Valve'.

The number of cells will vary as will the values. The
group will always start in cell A1.

I can add 2 worksheets, but they have the generic
names 'Sheet2' etc.

What is the best way of creating the worksheets and
renaming them?

Creating worksheets with cell values as names
 

Hi Bob,

With your names listed in column A , starting in A2 (to allow for a header),
try:

Sub AddSheets()
Dim LastCell As Range, Rng As Range, Cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp)
Set Rng = WS.Range("A1", LastCell)
For Each Cell In Rng
If Not IsEmpty(Cell) Then
Sheets.Add.Name = Cell.Value
End If
Next

End Sub



---
Regards,
Norman


"Bob" wrote in message
...
Hello

From a group of cells in a worksheet I want to create
worksheets in the same workbook, each of which has the
name of the contents of the cells (all strings).

for example:
value in cell A1 is 'Ball Valve'
valve in cell A2 is 'Globe Valve'

I want to create 2 worksheets: one named 'Ball Valve', the
other 'Globe Valve'.

The number of cells will vary as will the values. The
group will always start in cell A1.

I can add 2 worksheets, but they have the generic
names 'Sheet2' etc.

What is the best way of creating the worksheets and
renaming them?

Creating worksheets with cell values as names
 

There's no logic in what you're saying..
A1= Ball
A2= Globe

How does that tally with: the group always starts in a1 ??
I assume you DONT want to create a sheet for each CELL in a range?

Please try to rephase it in such a way that there's some logical flow
that a programmer can put into code ?


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Bob wrote :

Hello

From a group of cells in a worksheet I want to create
worksheets in the same workbook, each of which has the
name of the contents of the cells (all strings).

for example:
value in cell A1 is 'Ball Valve'
valve in cell A2 is 'Globe Valve'

I want to create 2 worksheets: one named 'Ball Valve', the
other 'Globe Valve'.

The number of cells will vary as will the values. The
group will always start in cell A1.

I can add 2 worksheets, but they have the generic
names 'Sheet2' etc.

What is the best way of creating the worksheets and
renaming them?

Creating worksheets with cell values as names
 

Hi Bob,

Forgot to change:

Set Rng = WS.Range("A1", LastCell)

to

Set Rng = WS.Range("A2", LastCell)


to allow for the mentioned header!


---
Regards,
Norman


"Norman Jones" wrote in message
...
Hi Bob,

With your names listed in column A , starting in A2 (to allow for a
header),
try:

Sub AddSheets()
Dim LastCell As Range, Rng As Range, Cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp)
Set Rng = WS.Range("A1", LastCell)
For Each Cell In Rng
If Not IsEmpty(Cell) Then
Sheets.Add.Name = Cell.Value
End If
Next

End Sub



---
Regards,
Norman


"Bob" wrote in message
...
Hello

From a group of cells in a worksheet I want to create
worksheets in the same workbook, each of which has the
name of the contents of the cells (all strings).

for example:
value in cell A1 is 'Ball Valve'
valve in cell A2 is 'Globe Valve'

I want to create 2 worksheets: one named 'Ball Valve', the
other 'Globe Valve'.

The number of cells will vary as will the values. The
group will always start in cell A1.

I can add 2 worksheets, but they have the generic
names 'Sheet2' etc.

What is the best way of creating the worksheets and
renaming them?

Creating worksheets with cell values as names
 

Thanks Norman.
-----Original Message-----
Hi Bob,

Forgot to change:

Set Rng = WS.Range("A1", LastCell)

to

Set Rng = WS.Range("A2", LastCell)


to allow for the mentioned header!


---
Regards,
Norman


"Norman Jones" wrote in
message
...
Hi Bob,

With your names listed in column A , starting in A2 (to
allow for a
header),
try:

Sub AddSheets()
Dim LastCell As Range, Rng As Range, Cell As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set LastCell = WS.Cells(Rows.Count, "A").End(xlUp)
Set Rng = WS.Range("A1", LastCell)
For Each Cell In Rng
If Not IsEmpty(Cell) Then
Sheets.Add.Name = Cell.Value
End If
Next

End Sub



---
Regards,
Norman


"Bob" wrote in
message
...
Hello

From a group of cells in a worksheet I want to create
worksheets in the same workbook, each of which has the
name of the contents of the cells (all strings).

for example:
value in cell A1 is 'Ball Valve'
valve in cell A2 is 'Globe Valve'

I want to create 2 worksheets: one named 'Ball
Valve', the
other 'Globe Valve'.

The number of cells will vary as will the values. The
group will always start in cell A1.

I can add 2 worksheets, but they have the generic
names 'Sheet2' etc.

What is the best way of creating the worksheets and
renaming them?







.