Programmatically Rename Excel Sheets

  • I have a workbook project that generates a large number of new spreadsheets each time it is run.

    I would like to programmatically rename these spreadsheets as they are created, using a value found in cell A1.

    Can anyone give me an example of how this would be done?

    Thanks in advance -


  • This will name the activesheet whatever value is found in cell A1 of that sheet:

    ActiveSheet.Name = ActiveSheet.Range("A1").Value

    This will name the second sheet in the workbook whatever value is found in cell A1 of the first worksheet:

    Sheets(2).Name = Sheets(1).Range("A1").Value

    Hope this helps you out.

    Barrie Davidson
    My Excel Web Page
    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  • I am sure you realize that you will not be able to name all of your sheets based on one static name in A1, Excel will not allow you to name your sheets the same. I am assuming you are placing an array of names to be inserted or have some counter to add to the end of your names. Something similar to;

    Sub ChangeWSName()

    Dim i As Integer
    Dim WSCount As Integer
    WSCount = Worksheets.Count

    Do While i < WSCount

    i = i + 1
    Sheets(i).Name = Range("A1").Value & i


    End Sub


Participate now!

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