Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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? |
#11
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
Creating a formula that references other sheets | Excel Worksheet Functions | |||
Multiple sheets selected | Excel Discussion (Misc queries) | |||
Creating Individual Excel Files from Sheets | Excel Discussion (Misc queries) | |||
Ignoring characters in excel sheets when creating a chart | Charts and Charting in Excel |