Creating worksheets with cell values as names

B

Bob

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?
 
N

Norman Jones

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
 
K

keepITcool

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 :
 
N

Norman Jones

Hi Bob,

Forgot to change:

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

to

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


to allow for the mentioned header!
 
B

Bob

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


allow for a
header),


.
Click to expand...
 
Top