Forum Discussion

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

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

Copper Contributor

Jun 26, 2018

Getting error here

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

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.
- kelly johnson
  Copper Contributor
  Jun 26, 2018
  Yes, that was my first mistake. :) I stepped through it and have an error show up at that line.
  - kelly johnson
    Copper Contributor
    Jun 26, 2018
    Well I'll do a compare, I copied yours and replaced mine and it worked, so thank you.
    I'll checkout the difference when I got time, now I'm being pulled in a million directions. :)
    Thank you again!