Create New Sheets based on Criteria and Multiple Templates

Good Morning,

I need to create multiple sheets in a workbook based on two lists.

As you can see I 6 sheets (EA,SY,SF,CY,LF,TON) those are my templates. I need to first have the code look at the data in column D3 to however long the range is and if it matches the template sheet name then create a new sheet based on that template.

I then need to have the new sheets created renamed to the data located on column A of that same row.

for example using A3 the code would create a new sheet named 145 using the template for EA. I'm not sure how possible this is.

Thanks


Excel.jpg

You could use this code:

Please Login or Register  to view this content.

Regards,
Antonio

That seems to be working, it's picking the right template and renaming it correctly but a few issues.

1) I'm getting a run-time error "9" subscript out of range after the second sheet is created.
2) Also I might have not been clear in my explanation, I don't want it copying the row A3 onto the new sheets.

Attachment 425050

If anything I need to copy the data on columns A-C to its corresponding new sheet and put them in the correct spot on the new sheet.
I've included the photo above to show you and all my templates headers are all the same format

So item 145 for example would create a new sheet with the EA template then copy the Item (Cell A3 'Estimates') to cell B5 (Item 145 Sheet), the Description (Cell B3 'Estimates') to cell A1 (Item 145 Sheet) and the Original contract qty (Cell C3 'Estimates') to cell B6 (Item 145 Sheet)