Copy and paste based on two values

I have been working on this for a couple days and can't get it right. I need a macro to read a value in B2 from the second sheet and then find the row of data based on that value from the first sheet. However, the sheets we use have multiple data sets that are then calculated and I only need the data from that found row for the columns marked SS. So, the part I'm getting confused is how would I be able to get it to copy both the titles and the data based on a column marked SS after finding the row from the earlier criteria. In short, I want it to match lot #'s from sheet two and then only bring over data that the headers are marked SS to sheet 2 including the titles. It can be pasted anywhere for now as I will tune it up later once I figure out all the details. I am updating the old macro so that if we change things on one sheet, the macro will not get messed up and lose references. I know basic VBA and will edit everything to make it work on all the files, so I am attaching a simplified version that once I know the code, I can apply it. I give examples in the spreadsheet as well. Thanks in advance.


test1.xls

I placed the data in the Sheet "CofA" starting at row 12.

Please Login or Register  to view this content.

The macro works and does what I want it to do as far as the example, but instead of having the SS columns defined in the array as 6,7,8, etc., is it possible to make excel search for the SS columns and copy the data based on that search in addition to the previous search for the lot number. Say I add a column between Title 5 & 6, that is not SS, I would have to go back in and redefine the columns (which for me is not an issue, but others here don't know VBA). Is there a way to make it dynamic?

Please Login or Register  to view this content.

Perfect! Works like a charm and is exactly what I wanted.