Excel - A macro to create and name worksheets based on a list

October 2017


I need a macro that can automatically create and rename worksheets, based on a list that exists in another sheet ("Summary") of the same workbook? The list begins at cell A10. How can this be implemented under Excel?


Try this:

Sub CreateSheetsFromAList() 
    Dim MyCell As Range, MyRange As Range 
    Set MyRange = Sheets("Summary").Range("A10") 
    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 
End Sub 

Thanks to Ivan-hoe for this tip.


Published by jak58. Latest update on February 13, 2013 at 11:31 AM by jak58.
This document, titled "Excel - A macro to create and name worksheets based on a list," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).