Create multiple sheets based on Master sheet criteria

[JAC0617]

Hi,

I have spent the last few hours searching the forums for similar requests and although I found some similar, none seemed to match what I'm look for help with so apologize in advance if it already exists. I have an Excel file with 7 sheets - Master, Template1, Template2, Template3, Template4, Template5 and Template 6. The "Master" sheet has values in column A through C. I'm trying to create a new sheet for each row if the value in column B is "Y". If so, the use Column A to name the new sheet and Column B to determine the sheet to copy. On other criteria would be for the macro to not delete any existing sheets created with the same sheetname.

Much appreciated for any assistance.

 

[mumps]

Column B to determine the sheet to copy.

Didi you mean column C?

On other criteria would be for the macro to not delete any existing sheets created with the same sheetname.

Excel does not allow more than one sheet with the same name. Are you saying that a sheet named using the data in column A may already exist?

 

[JAC0617]

Didi you mean column C?

Excel does not allow more than one sheet with the same name. Are you saying that a sheet named using the data in column A may already exist?

Sorry for not being clearer. In the event I need to update column B from N to Y for a row, I don't want any existing sheets previously created to be overridden since the templates, once assigned, will have manual inputs entered.

 

[mumps]

The sheet to copy will be in column C. Is this correct? If a sheet was previously created, it will have the name using column A. For example, a sheet named P00001 has already been created. Then you change cell B2 to "Y" and another sheet should be created using P00001. This will generate an error because a sheet with that name already exists. Please clarify in detail.

 

[JAC0617]

The sheet to copy will be in column C. Is this correct? If a sheet was previously created, it will have the name using column A. For example, a sheet named P00001 has already been created. Then you change cell B2 to "Y" and another sheet should be created using P00001. This will generate an error because a sheet with that name already exists. Please clarify in detail.

Yes. Based on the example, 3 sheets would be created with the names P00001, P00005 and P00015 when the macro is run. If subsequent to the macro being run, cell B4 is changed to Y and the macro is re-run, I don't any existing sheets (P00001, P00005 and P00015) to be copied over with the blank templates or have them be deleted and recreated.

 

[JAC0617]

Yes. Based on the example, 3 sheets would be created with the names P00001, P00005 and P00015 when the macro is run. If subsequent to the macro being run, cell B4 is changed to Y and the macro is re-run, I don't any existing sheets (P00001, P00005 and P00015) to be copied over with the blank templates or have them be deleted and recreated.

Btw, I'm assuming a macro is a solution since that is what I used many years ago for similar type tasks.

 

[mumps]

Try:

Sub CopySheet()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long
    v = Sheets("Master").Range("A2", Sheets("Master").Range("A" & Rows.Count).End(xlUp)).Resize(, 3).Value
    For i = LBound(v) To UBound(v)
        If v(i, 2) = "Y" Then
            If Not Evaluate("isref('" & v(i, 1) & "'!A1)") Then
                Sheets(v(i, 3)).Copy after:=Sheets(Sheets.Count)
                ActiveSheet.Name = v(i, 1)
            End If
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[JAC0617]

Try:

Sub CopySheet()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long
    v = Sheets("Master").Range("A2", Sheets("Master").Range("A" & Rows.Count).End(xlUp)).Resize(, 3).Value
    For i = LBound(v) To UBound(v)
        If v(i, 2) = "Y" Then
            If Not Evaluate("isref('" & v(i, 1) & "'!A1)") Then
                Sheets(v(i, 3)).Copy after:=Sheets(Sheets.Count)
                ActiveSheet.Name = v(i, 1)
            End If
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Thank you! I will try it out with more data this evening, but looks like it did the trick. Thanks again for the quick response and have a great Sunday.

 

[mumps]

You are very welcome.