How to create worksheets from a list using a worksheet template

[smileytmroper]

I have a worksheet with a list of information in it. I know how to generate new worksheets that are named from this list. What I want to do is create new worksheets from the list that are based on a worksheet template so I can save a lot of data entry. Thanks

 

[Ken Cowen]

It should be pretty easy, but people need to know how the information is arranged before taking a guess a how it should work. Explain a little about the "list of information". Do you have a column of sheet names, are there actually multiple columns in your "list" or does the information in the "list" need to be parsed; and, how does it map to the new worksheet created from the template? Is the template a sheet in the workbook or does it come from somewhere else? If you can explain the rules that need to be followed, is will be easy to turn those rules into VBA code.
Ken

 

[smileytmroper]

I will call the worksheets for the purposes here "Master List" and "Template". The Master List has several columns of data, however I only need the data in Column A to create the new worksheets. The Template that I would like to use to base the new worksheets on will have formulas to lookup the additional data that is needed off the "Master List" sheet.
Also if it is possible I would like it to be dynamic so that when I add a new entry to column A, or remove and entry from column A it will add or remove the worksheet. (Not sure if this is possible, but hey why not shoot for the stars.

 

[Ken Cowen]

If you put the following code in the code module for the Master List worksheet, then whenever you enter something in column A, the template will be copied to the far right, and renamed to match the data that was entered into column A. The Master List worksheet remains the active sheet. I am sure this is not exactly what you want, but it should get you started. As far as deleting, that is a bit more complicated to do on entry/deletion. You will probably need to trigger a procedure that loops through all the worksheets an deletes all that don't have a corresponding entry in column A of the Master LIst worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)


Dim ws As Worksheet

Set ws = ActiveSheet

If Not Intersect(Target, ActiveSheet.Columns("A:A")) Is Nothing Then

If Target.Value <> "" Then
Sheets("template").Select
Sheets("template").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Target.Value
End If

End If

ws.Activate


End Sub



I hope this helps.

Ken

 

[smileytmroper]

Thanks for your help Ken. Will give it a try in the morning.

 

[smileytmroper]

Works awesome Ken. Thank you for your help.