A question from Askville:
How can I insert worksheets in excel and name them automatically from a list?
In an excel workbook, I have a list of 36 words and I want a separate worksheet for each of them – all named.
Write a macro. Something along the lines of:
Sub CreateAndNameWorksheets() Sheets("Words").Select Words = Cells.Range("A1:A36") For Each Item In Words Sheets.Add After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Name = Item Next Item End Sub
The code above assumes that your list of words is stored in cells A1:A36 of the worksheet named Words.
I can’t get your Sub to run. Gets a “sub/function not defined.” I played with it and got past that, but now I get Syntax errors for the lines “For Each Item In Words” and “Sheets.Add After:=Sheets(Sheets.Count).
I don’t really have a grasp of VB, so the syntax stumps me. I am testing this in Excel 2003, but was hoping to use it in a Excel07 file.
Thanks for any pointers.
The notes to the code say plainly, “The code above assumes that your list of words is stored in cells A1:A36 of the worksheet named Words.” If you don’t have such a worksheet in your workbook, the Words variable never gets populated, so it doesn’t have a Range or Array type, so an attempt to use it as an argument in a “For Each” construct throws an error, as For Each expects an enumeration of some kind, over which it could iterate.
I deliberately didn’t write any error-checking, since the idea was to show how to do stuff with available data. Putting error-checking in place would have obscured the functionality…
The code works great for me, thanks a million! I only have one thing next to figure out. When I load the code, it works fine as long as I have a value in the range of cells. My range will never exceed a1:36, but will not always be up to 36. One day may be 24 and the next 34. I dont want to write a new code each time. Can I enter the code somehow, that will only creat a worksheet if the cells in that range have a value of <0?
Hello,
The above code works just how I need thanks, however I want to slightly mod it so that instead of it adding a new set of worksheets from the list, I want to to copy a master worksheet and rename it using the list.
I am not even anywhere near being good with VB so any help would be great.
I have tried changing it to use Sheets.Copy but it just copies every worksheet over and over and also new ones using the list.
Thanks
Drew,
Instead of Sheets.Copy, you should try Sheets(“SheetName”).Copy. Something along these lines:
Sub CopyMasterSheet()
Sheets("Master").Select
Words = Cells.Range("A1:A4")
For Each Item In Words
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Item
Next Item
End Sub
Hi.. I should prob be able to figure this out myself but clearly I am no good w/ VB. I’m looking to mod the code to name each sheet (25 total) based on the text in cell C4 of each sheet. How would I modify? And where would I place the code–in each worksheet or in the workbook?
Using Excel 03.
Thanks in advance
Dave
OMG. You are a genius. I am a complete novice in using html (or whatever this code is?!?) and this worked for me! Thank you for saving me several hours of work!
Lynn,
I am no genius, but I am happy that you found it useful. And, just so you know, it’s called VBA (Visual Basic for Applications).
NC I get Run-time error ‘1004’:
You typed an invalid name for a sheet or chart. Make sure that:
>The name that you type does not exceed 31 characters.
>The name does not contain any of the following characters: :\/?*[ or ]
>You did not leave the name blank.
The issue is with Sheets(Sheets.Count).Name = Item
Sub DailyReportPopulator()
Sheets(“Executive_Summary”).Select
Executive_Summary = Cells.Range(“B4:B59”)
For Each Item In Executive_Summary
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Item
Next Item
End Sub
Any help would be greatly appriciated.
Thanks,
David,
Check the data in your Executive_Summary!B4:B59. Looks like some of the values contained in the cells of this range can’t be used as worksheet names, because they are either too long, or empty, or contain characters not allowed in worksheet names.
I am looking to do something very similar. Only I need the first sheet of the workbook to be a log of all the sheets following it. Which all have the same form that will be populated with different information but not the same form as the log sheet.
If all of the sheet names were created from a new entry in a list in the log sheet like (a1:a2000). That would work. But it would be nice if it worked both ways. Where if I picked on the new sheet tab it would make the new sheet from the form and auto name it based on the last sheet name +1 as the sheet names will all be a 5 digit numeric value in sequence from 00001 to 99999 and create the new row with this sheet name in A column in the log file. Also I need the log to access the information in certain cells of the new sheet that is associated with the sheet name in the A column of the log sheet and displayed in the row associated with the sheets name in the log sheet. I am fully aware that beggars can’t be chooser’s and since I don’t grasp VB code at all. I will be ecstatic with anything that will accomplish this. either way.
Chuck,
Off the top of my head, something like this should work:
This assumes that the first sheet is called TOC.
Automatic naming is also doable, but you’ll need to put the code into ThisWorkbook object, rather than into a module were it would go by default:
As to the whole logging thing, I am simply not sure I understand your requirements…
This is what I have, but I keep getting the error, “Compile error: Expected End Sub”
Sub AutoPop()
‘
‘ AutoPop Macro
‘
‘ Keyboard Shortcut: Ctrl+Shift+P
‘
Sub CreateAndNameWorksheets()
Sheets(“Test1”).Select
Words = Cells.Range(“A2:A9”)
For Each Item In Test1
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Item
Next Item
End Sub
Any help would be much appreciated.
I want to hve a work sheet created automatically when ever i feed the data in the first col of the first sheet named master sheet
Thanks a heap! I have tried others but they reversed the sheets (ie the one I wanted first was last and they entered after my first sheet) Cheers!