Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using contents of a cell as the sheet name in another formula | Excel Discussion (Misc queries) | |||
transfer contents from a cell in previous sheet if the sheet is a | New Users to Excel | |||
Displaying cell contents on one sheet | Excel Discussion (Misc queries) | |||
Lookup cell contents in on sheet based on a formula in second sheet | Excel Worksheet Functions | |||
How do I automatically rename a sheet with the contents of a cell. | Excel Discussion (Misc queries) |