New sheet from the cell contents

[John]

Hi falks,
Is it possible to create new sheets with the cell contents in VBA?
For e. g.
A
1 john
2 tom
3 roger

I want to create new sheets named john, tom and roger by running a VBA.
Is it posible?

Thanks,
John

 

[Chip Pearson]

John,

Try something like the following. Change A1:A10 to the appropriate range.


Dim Rng As Range
For Each Rng In Worksheets("Sheet1").Range("A1:A10")
If Rng.Text <> "" Then
With Worksheets
.Add(after:=.Item(.Count)).Name = Rng.Text
End With
End If
Next Rng


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

 

[John]

Perfectly fine. But now another problem popup. If I already have the sheet with that name macro gives error. How to skip creating sheet if it already exists

----- Chip Pearson wrote: ----

John

Try something like the following. Change A1:A10 to the appropriate range


Dim Rng As Rang
For Each Rng In Worksheets("Sheet1").Range("A1:A10"
If Rng.Text <> "" The
With Worksheet
.Add(after:=.Item(.Count)).Name = Rng.Tex
End Wit
End I
Next Rn


--
Cordially
Chip Pearso
Microsoft MVP - Exce
Pearson Software Consulting, LL
www.cpearson.com (e-mail address removed)

 

[Tom Ogilvy]

Dim Rng As Range
Dim sh as Worksheet
For Each Rng In Worksheets("Sheet1").Range("A1:A10")
If Rng.Text <> "" Then
On error resume next
set sh = Worksheets(rng.text)
On error goto 0
if sh is nothing then
With Worksheets
.Add(after:=.Item(.Count)).Name = Rng.Text
End With
End if
End If
Next Rng


or

Dim Rng As Range
For Each Rng In Worksheets("Sheet1").Range("A1:A10")
If Rng.Text <> "" Then
On Error Resume Next
With Worksheets
.Add(after:=.Item(.Count)).Name = Rng.Text
End With
On Error goto 0
End If
Next Rng


--
Regards,
Tom Ogilvy

Perfectly fine. But now another problem popup. If I already have the sheet

with that name macro gives error. How to skip creating sheet if it already
exists?

 

[John]

Hi
I run both the macro but there is a small problem. If there is a sheet already exists, either is stops running further or it simply creats sheets named sheet11, sheet12 sheet13 etc for all repeating names. This is troublesome. If the sheet already exists,
Macro should go to the next cell and continue creating the balance sheets. Can it be done? Sorry for my poor english

Thanks
Joh

----- Tom Ogilvy wrote: ----

Dim Rng As Rang
Dim sh as Workshee
For Each Rng In Worksheets("Sheet1").Range("A1:A10"
If Rng.Text <> "" The
On error resume nex
set sh = Worksheets(rng.text
On error goto
if sh is nothing the
With Worksheet
.Add(after:=.Item(.Count)).Name = Rng.Tex
End Wit
End i
End I
Next Rn


o

Dim Rng As Rang
For Each Rng In Worksheets("Sheet1").Range("A1:A10"
If Rng.Text <> "" The
On Error Resume Nex
With Worksheet
.Add(after:=.Item(.Count)).Name = Rng.Tex
End Wit
On Error goto
End I
Next Rn


-
Regards
Tom Ogilv

Perfectly fine. But now another problem popup. If I already have the shee

with that name macro gives error. How to skip creating sheet if it alread
exists

 

[Dave Peterson]

If you want the cell skipped, this minor modification to Tom's code worked ok
for me:

Option Explicit
Sub test1()
Dim Rng As Range
Dim sh As Worksheet
For Each Rng In Worksheets("Sheet1").Range("A1:A10")
If Rng.Text <> "" Then
Set sh = Nothing '<-- added
On Error Resume Next
Set sh = Worksheets(Rng.Text)
On Error GoTo 0
If sh Is Nothing Then
With Worksheets
.Add(after:=.Item(.Count)).Name = Rng.Text
End With
End If
End If
Next Rng
End Sub


The second version creates the new worksheet and then renames it. (So it
doesn't sound like that version is for you.)