Naming Newly Created Sheet

  • This is probably an easy answer... I have a template sheet that performs a calculation dependent upon a unit number. I want to loop through all unit numbers and create copy of the template sheet for each unit. I believe I can make everything work except for naming the newly created sheet. I don't know what generic name Excel will give it, so I need to name the active sheet. Activesheets.Name didn't work. The name will be the unit name listed in cell B2.

    Thanks for any help.


  • Hi Chris,

    Excel gives the new sheet the generic name, The old sheets name + a number
    E.g. Sheets(“Temp”).Copy After:=Sheets("Amenity") gives a sheet with the name “Temp (2)” and copy again results in “Temp (3)”
    So if you want to make a new sheet for each unit number in the column B and name them after the cell values in the column, you could write

    Have a nice day


  • The newly added sheet will then be active so perhaps try something like this:

    Sub Test()
    Worksheets.Add ' Add a worksheet
    ActiveSheet.Name = "test" ' Name a worksheet.
    End Sub


    <a href="" target="new"><img src=""></a>

  • Thanks guys!

    The method I used was to change the sheet Temp (2) to a the new file name in each loop. Then, the next sheet created would be Temp (2) also.

    As for the pivot table suggestion, I needed all of these to be in seperate sheets for distribution purposes.

    Thanks again for the responses.

    This really is a terrific forum!


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!