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.


    Chris

  • 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


    Woody

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


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


    HTH

    <a href="http://www.mrexcel.com/relayforlife.shtml" target="new"><img src="http://www.myimgs.com/data/vonpookie/anne_relay.gif"></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!


    Chris

Participate now!

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