Creating new sheets
 

Hey guys,

I have a list of names and I need the number of names in that list to
be the number of automatically generated new sheets, each named a
different number.

so:

01 bob Brown
07 Jim Scott
11 Dave John

would become three different sheets, the first one named on the bottem
tab 01, the second - 07 and the third 11.

Any Ideas anyone?


Cheers

Hayley


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=471093

Good afternoon Hayley

Do you want to create a number of sheets that are to be named according
to the contents of cells? If so, then use this routine:

Sub SheetName()
For Each UsrCell In Selection
Sheets.Add.Name = UsrCell.Value
Next UsrCell
End Sub

Highlight the range of cells you want to use as the base, and then run
the macro.

HTH

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=471093

Thats grand, but how do i get it to run until the list is empty?


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=471093

Okdookie,

So I have my sheets, how do I make them so they have a template on
then?

The same information on all of them in the same place..?


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=471093

Hi HRU48

Not quite sure what you mean here. You're going to have to expand on
that.

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=471093

ok, at the minute I have the macro creating new and empty sheets, how
can i get it to create sheets with an applied style? say... is cell A1
i have the word bob etc?

and much thanks for you help so far!


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=471093

Hi HRU48

Still a bit lost here, but bear with me.

Does the macro I supplied do what you wanted?

Secondly, can you just pad out a bit more what you want, being
specific. You want bob in cell A1 of which sheet, what do you want in
cell A2. Do you want bob in every single sheet?

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=471093

Sorry for vagueness its getting towards the end of the day... :(

The code you gave me was great but how can I get it so the sheets are
not blank.

hmmm.. This is for a report i run every month - there is standard title
in the centre of the page and then the persons name and below tha a
segment where i refreash a query.

I have 1 copy of the sheet i described above but how would i get it so
that sheet was replicated throughout the workbook exactly the same on
every sheet?


is that any better?


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=471093

Hi Hayley

I think so. Yes, it's been a long day so far.

The code I've come up with is:

Sub SheetName()
For Each UsrCell In Selection
Sheets.Add.Name = UsrCell.Value
Range("A1").Value = "This is your title"
Range("A2").Value = UsrCell.Value
With Range("A1").Font
.Bold = True
.Size = 14
End With
Next UsrCell
End Sub

So this puts a title on the top row, and the users name below. The
With..End With construct just makes your title a bit fancier and can be
omitted if you like, it just shows how you can expand on the code to
tailor it to your needs.

Are we there yet?

HTH

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=471093

You would start out with two sheets.

One is a sheet with your list of your numbers/names.

One sheet is a template sheet with the formatting and data you wish to copy.

Sub testme01()
'Dave Peterson
Dim TemplateWks As Worksheet
Dim ListWks As Worksheet
Dim ListRng As Range
Dim myCell As Range

Set TemplateWks = Worksheets("Template")
Set ListWks = Worksheets("list")

With ListWks
Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In ListRng.Cells
TemplateWks.Copy After:=Worksheets(Worksheets.Count)
On Error Resume Next
ActiveSheet.Name = myCell.Value
If Err.Number < 0 Then
MsgBox "Please fix: " & ActiveSheet.Name
Err.Clear
End If
On Error GoTo 0
Next myCell
End Sub


Gord Dibben Excel MVP

On Tue, 27 Sep 2005 11:33:44 -0500, Hru48
wrote:


Sorry for vagueness its getting towards the end of the day... :(

The code you gave me was great but how can I get it so the sheets are
not blank.

hmmm.. This is for a report i run every month - there is standard title
in the centre of the page and then the persons name and below tha a
segment where i refreash a query.

I have 1 copy of the sheet i described above but how would i get it so
that sheet was replicated throughout the workbook exactly the same on
every sheet?


is that any better?

Thanks for your help guys!!


--
Hru48
------------------------------------------------------------------------
Hru48's Profile: http://www.excelforum.com/member.php...o&userid=24895
View this thread: http://www.excelforum.com/showthread...hreadid=471093