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

November 2016


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.

Related :

This document entitled « Excel - A macro to create and name worksheets based on a list » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.