New sheet from the cell contents
 

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

New sheet from the cell contents
 

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

New sheet from the cell contents
 

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



"John" wrote in messag
..
Hi falks
Is it possible to create new sheets with the cell contents in VBA
For e. g

1 joh
2 to
3 roge
I want to create new sheets named john, tom and roger by running a VBA
Is it posible
Thanks
Joh

New sheet from the cell contents
 

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

New sheet from the cell contents
 

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


John wrote in messag
..
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
----- Chip Pearson wrote: ----
John
Try something like the following. Change A1:A10 to the appropriat
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
"John" wrote in messag
..
Hi falks
Is it possible to create new sheets with the cell contents in VBA
For e. g

1 joh
2 to
3 roge
I want to create new sheets named john, tom and roger by running
VBA
Is it posible
Thanks
Joh

New sheet from the cell contents
 

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