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.)

John wrote:

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,
John

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

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


John wrote in message
...
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 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
"John" wrote in message
...
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


--

Dave Peterson