Create worksheets with names from a list [Solved/Closed]

- - Latest reply: ac3mark
Posts
13034
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
- Oct 16, 2015 at 04:56 PM
Hello,

I would be most grateful if you would kindly help me out.
I have a long list of projects and need to create an individual worksheet for each project

Is it possible to automatically create these in Excel ?

Many thanks in advance!

See more 

1 reply

Posts
13034
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1539
0
Thank you
OK so you want to hook into another external list, and create a tab for it? That is really complicated, with path names and such. Let us start with dynamically creating the tab. what do you think about starting there?

Let us know where you get stuck, as we do not provide turn key solutions, but help when stuck. Post some code and we can guide you. I like to start with making a MACRO and reverse-engineering it to fit my needs. Like so:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/15/2015 by a BAD-A$$ MFR
'

'
    Sheets("Sheet3").Select
    Sheets.Add
    Sheets("Sheet1").Select
    Range("A4").Select
    Selection.Copy
    Sheets("Sheet4").Select
    Sheets("Sheet4").Name = "tab four"
End Sub



You will notice where I select Sheet1, cell A4, that value is what was copied, which happened to be the name of the tab "tab 4".

I hope this makes sense, as now all you need to do is loop through an array, creating and renaming each tab!

Have FUN!
I have said it once, I will say it again. IT!
Many thanks for taking the time to answer me but I'm afraid your solution may be a little over my head.

I have replaced "sheet 3" above with the name of the worksheet that contains the list of names for the new worksheets ("SUMMARY ALL NOMINALS"). the cell with the first worksheet name is cell "U4" . The other required new worksheet names are in cells U5, U6 , U7 etc

Cab you see where I have gone wrong from the below at all ?

Sub LISA()

Sheets("SUMMARY ALL NOMINALS").Select
Sheets.Add
Sheets("SUMMARY ALL NOMINALS").Select
Range("u4").Select
Selection.Copy
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "u4"
> Child@heart -
I have a solution.! Thank you for your help. The below worked

Regards and happy weekend

Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Summary all nominals").Range("U4")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Next MyCell
ac3mark
Posts
13034
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1539 -
Excellent, I am glad I could HELP!