Create multiple sheets from master named from list

[Andreavnn]

Hello.

I have a file that has a master sheet that is formatted for a specific use. I have a list of part names on another sheet, there are 930 different parts. I am needing help creating a new copy of the master for each different part within the same excel document. Each sheet needs to be named after a different part. Basically, I need a copy of the master sheet for each unique item in column A and cell B1 to be named after the sheet name itself.

All unique items in column A need their own copy of the master sheet and their sheets need to be named - then cell B1 on the new sheets need to reflect the name of the sheet.

Hope that makes sense. Thank you!

 

[mumps]

Try:

Sub CreateSheets()
    Application.ScreenUpdating = False
    Dim i As Long, v As Variant
    v = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(v, 1)
            If Not .Exists(v(i, 1)) Then
                .Add v(i, 1), Nothing
                Sheets("Master").Copy after:=Sheets(Sheets.Count)
                ActiveSheet.Name = v(i, 1)
                Range("B1") = v(i, 1)
            End If
        Next i
    End With
    Application.ScreenUpdating = True
End Sub

 

[Andreavnn]

I am getting an error. It is creating a sheet, but can't seem to change the name.

It seems to also be making copies of other sheets too.

Here are my sheet order

The master sheet is my template. Any sheet with (H) is typically hidden from view. I have a button on the input form that "actives" the creation of the sheets and my part list is on Data(H) sheet.

 

[mumps]

Try:

Sub CreateSheets()
    Application.ScreenUpdating = False
    Dim i As Long, v As Variant
    Sheets("Data").Visible = True
    v = Sheets("Data").Range("A2", Sheets("Data").Range("A" & Rows.Count).End(xlUp)).Value
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(v, 1)
            If Not .Exists(v(i, 1)) Then
                .Add v(i, 1), Nothing
                Sheets("Master Sheet").Copy after:=Sheets(Sheets.Count)
                ActiveSheet.Name = v(i, 1)
                Range("B1") = v(i, 1)
            End If
        Next i
    End With
    Sheets("Data").Visible = xlHidden
    Application.ScreenUpdating = True
End Sub

 

[Andreavnn]

This worked! But I have run into an error.

I didn't get a screen grab of the error, but is was something about the name wasn't or was over 31 characters. The error was some sort of naming convention error. It stopped on line 70 which reads "050/075PLBLANK".

Additionally, is there a way to add check into the code that will skip sheet that are ready created? How it is I can only run it once, which would normally be fine, but it make is hard to test when it makes half the sheets and then stops from any error and I have to create another copy of the master file to try another test.

Thank you so much by way, saving me tons of time not having to make the sheets manually!

 

[Andreavnn]

This worked! But I have run into an error.

I didn't get a screen grab of the error, but is was something about the name wasn't or was over 31 characters. The error was some sort of naming convention error. It stopped on line 70 which reads "050/075PLBLANK".

Additionally, is there a way to add check into the code that will skip sheet that are ready created? How it is I can only run it once, which would normally be fine, but it make is hard to test when it makes half the sheets and then stops from any error and I have to create another copy of the master file to try another test.

Thank you so much by way, saving me tons of time not having to make the sheets manually!

Here is the error

 

[Andreavnn]

Here is the error

View attachment 59248

I've figured it has to deal with the '/' in the name. There are a handful of items with a slash in their name. It looks like it can be removed and just replaced with a space which should work for the sheet name, but not the part name in B1. Not sure if there is a work around to be had here. Maybe it can skip part names with a slash and those can be created manually? I am open to suggestions on that part.

Sorry, for some many replies in a row.

 

[mumps]

The length of sheet names cannot exceed 31 characters. This macro should address all your issues. If the sheet names contain "/", it will be replaced with a space and if the name is longer than 31 characters, it will be shortened to 31.

Sub CreateSheets()
    Application.ScreenUpdating = False
    Dim i As Long, v As Variant, wsName As String
    Sheets("Data").Visible = True
    v = Sheets("Data").Range("A2", Sheets("Data").Range("A" & Rows.Count).End(xlUp)).Value
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(v, 1)
            wsName = Replace(Left(v(i, 1), 31), "/", " ")
            If Not .Exists(wsName) Then
                .Add wsName, Nothing
                If Not Evaluate("isref('" & wsName & "'!A1)") Then
                    Sheets("Master Sheet").Copy after:=Sheets(Sheets.Count)
                    ActiveSheet.Name = wsName
                    Range("B1") = wsName
                End If
            End If
        Next i
    End With
    Sheets("Data").Visible = xlHidden
    Application.ScreenUpdating = True
End Sub

 

[Andreavnn]

Working great. I am still running the code through for the first time so it is taking awhile, but so far no issues and it is all working. Thank you very much for helping and getting me pointed into the right direction with this.

Thank you! 10/10

 

[mumps]

You are very welcome.