Forum Discussion

kelly johnson's avatar
kelly johnson
Copper Contributor
Jun 26, 2018

Can I use a list to auto fill sheet tabs?

Can I use a list to auto fill sheet tabs instead of naming them one at a time?

excel

6 Replies

  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    Jun 26, 2018

    Kelly-

     

    Hope you're doing well.  Yes, this is possible but you'll need to use VBA:

     

    Sub Test()

Dim Lrow As Integer
    Dim intLp As Integer

Lrow = Sheets("MySheetNames").Cells(Rows.Count, "A").End(xlUp).Row

For intLp = 2 To Lrow
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(ActiveSheet.Name).Name = Sheets("MySheetNames").Range("A" & intLp)
Next intLp

End Sub

    • kelly johnson's avatar
      kelly johnson
      Copper Contributor
      Jun 26, 2018

      Getting error here

       

         Sheets(ActiveSheet.Name).Name = Sheets("MySheetNames").Range("A" & intLp)

      • Matt Mickle's avatar
        Matt Mickle
        Bronze Contributor
        Jun 26, 2018
        Did you name the worksheet with the names on it "MySheetNames"? If they are on another worksheet you will need to change this reference to the name of your worksheet.