A macro to create new, copy and name worksheets based on a list

Solved/Closed
chottabeem Posts 5 Registration date Tuesday July 19, 2016 Status Member Last seen July 22, 2016 - Jul 19, 2016 at 02:24 AM
vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 - May 11, 2017 at 12:52 AM
Greetings,
I need a macro that can automatically create new, copy (existing worksheet say '100') and rename the new worksheets, based on a list that exists in another sheet ("Summary") of the same workbook? The list begins from '100' at cell A2. How can this be implemented under Excel?

It will be great-full, if I am be helped with the solution.
Related:

11 responses

vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Jul 21, 2016 at 01:17 AM
Hello Chottabeem,

Assuming that you only want to create and name new sheets from the list in the Summary sheet, then the following code will do that:-

Sub CreateNameNewSheets()

    Dim LR As Long
    Dim c As Range
    Dim ws As Worksheet
LR = Range("A" & Rows.Count).End(xlUp).Row
    
For Each c In Range("A2:A" & LR)
        Set ws = Nothing
        On Error Resume Next
        Set ws = Worksheets(c.Value)
        If ws Is Nothing Then
        Worksheets.Add(After:=Sheets(Sheets.Count)).Name = c.Value
        End If
  Next c
End Sub


If you intend to use just numbers as the sheet names, then you will need to format those numbers as text otherwise, each time that you run the code, you will have additional and unwanted sheets added that will just have sheet numbers as names.

Run the code from the Summary sheet.

I hope that this helps.

Cheerio,
vcoolio.
chottabeem Posts 5 Registration date Tuesday July 19, 2016 Status Member Last seen July 22, 2016
Jul 21, 2016 at 03:06 AM
Hi Vcoolio,

Thanks for immediate reply. The code works good and creates new worksheets listed in "Summary".

Please refer to my file (shared as link http://speedy.sh/xcHkc/Macro-Temp.xlsm ) there are two sheets"Summary" consists of list and "Sheet1" having data.

The existing "Sheet1" (with data) to be renamed and copied along with data for the list in "Summary" (like, 10, 20, 30, 40.... 200)

Hope I clarified and expect the right solution.
0