VBA Code: Look for specific values and copy/paste

Boudifct

See if this helps:

http://www.mrexcel.com/forum/showthread.php?t=380786

Hi Peter,

I have tried and it did work, it indicates me that there is a mistake in the formula here (in bold):

=INDEX($A$1:$N$5,ROWS(C$9:C9),MATCH($A$9,$A$1:$N$1,0)-1+COLUMNS($C9:C9))

Moreover, since I have around 20 templates it it a bit different than your example. To be more specific the 20 templates are in the following area (A3:AL76).

It is why my first thought was for a VBA Code instead of a regular formula

Sorry I wanted to say that it did NOT work

upl, please

It is pretty unclear just how big each template is and what rows/columns they are in. Even your small example says each one is 4 rows and 5 columns but the samples look more like 5 rows and 4 columns (and what happened to column H in Template 2?).

So, what I suggest is that you set up a new test workbook with sheets and code just like below and see if that does the sort of thing you want. If so, see if you can modify the code to suit your actual data.

First, my 'templates' sheet (colours just to show the area of each of my templates).




Now my 'empty template' sheet. The template name is chosen in cell E1 by Data Validation drawn from a list in column K.




To implement ...

1. Right click the sheet name tab of the 'empty template' sheet and choose 'View Code'.

2. Copy the code below and paste into the main right hand pane that opens at step 1.

3. Close the VB window and try changing cell E1 to one of the other template names.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range("E1")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> Sheets("templates").Rows(3)<br>            Range("E3").Resize(5, 4).Value = .Find(What:=Target.Value, _<br>                After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, _<br>                MatchCase:=False, SearchFormat:=False).Offset(2).Resize(5, 4).Value<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>