VBA - copy different template sheets from a workbook, into multiple sheets of another workbook based on criteria on a summary excel sheet

[kira123]

I have 2 workbooks. The "master" workbook has a summary sheet with column A - List of names hyperlinked to a blank sheet each in the same workbook, the tabs are labelled the same as the name in the column. Column B has 1 or a combination of colour - there is 5 options (red, blue, green, blue & red, or red & green). I have a separate template workbook that has 5 template sheets each one corresponding to the colour: labelled red, blue, green, blue & red, or red & green. I want a macro that will go through column B of my "master" workbook, and depending on the colour, copy the corresponding template from the template workbook and then go back to the master workbook click through the link in the adjacent column A, which will take it through to an empty sheet and paste the template. This should repeat to go through the entire column. <tbody> </tbody> <code>Sub Summary() Dim MasterBook As Workbook Set MasterBook = ActiveWorkbook With MasterBook Dim rng As Range Set rng = Range("B:B") End With Dim TemplateBook As Workbook Set TemplateBook = Workbooks.Open(Filename:="C:\Users\Desktop\Example template.xlsx") Dim cell As Range For Each cell In rng If cell.Value = "Red" Then cell.Offset(0, -1).Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True TemplateBook.Sheets("Red").Copy ActiveSheet.paste ElseIf cell.Value = "Blue" Then cell.Offset(0, -1).Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True TemplateBook.Sheets("Blue").Copy ActiveSheet.paste End If Next cell End Sub</code> I've been trying to get the code to work for the past week with no luck. I tried various modifications, which ends up giving different error codes. The first Error I was getting was with <code>Set rng = Intersect(.UsedRange, .Columns(2))</code> “Object doesn’t support this property or method” So then I changed this to just going through the entire column just to see if it would work. <code>Set rng = Range("B:B")</code>. When I do that then it reads through and I get an error for <code>Set HyperlinkedBook = Workbooks.Open(Filename:=cell.Offset(0, -1).Value)</code> with the error code: run time error 1004 Sorry we couldn’t find 24 James.xlsx. Is it possible it was moved, renamed or deleted?” I believe that this line of the code is assuming that the hyperlink should open a different workbook with that name, however this is not the case. The hyperlink on the summary sheet links through to other sheets on the same master workbook, only the templates are on a separate book. So to overcome this I tried changing this line as well and ended up with the code above, which manages to open the template workbook, and copy just the tab name onto the first sheet and then gives an error for the following line <code>TemplateBook.Sheets("Red").Copy ActiveSheet.Paste</code>, saying “subscript out of range” I tried several more variations but I just can’t get it to copy the correct template, switch back to the master workbook, follow through the link on the summary sheet to the correct sheet (within the same master workbook), and paste the template.

<tbody>
</tbody>