Macro to copy data from one sheet to another depending on name in first cell

[subtilty]

Hi All,

I am having troubles getting a Macro to work with the sheet I have attached.

Basically I need it to loop down the 'Data Analysis' sheet and for each Component in Column A (1-10), copy the line and then paste it for this month on the sheet 'UK' in the row marked as the same Component number.

The attachment should make it clearer.

I hope that makes sense.

Thanks in advance!
James

[subtilty]

Currently I have this to do the loop:

Sub Test1()
      Dim x As Integer
      NumRows = Range("A3", Range("A3").End(xlDown)).Rows.Count
      Range("A3").Select
      For x = 1 To NumRows
      Range(ActiveCell, ActiveCell.Offset(0, 13)).Copy
      Sheets("test").Select
      Range("A1:O60").Find("").Select
      Selection.PasteSpecial Paste:=xlValues
         Sheets("Data Analysis").Select
         ActiveCell.Offset(1, 0).Select
      Next
End Sub

The part I don't get is, how can I copy the range here 'Range(ActiveCell, ActiveCell.Offset(0, 13)).Copy' but also do a search to find the correct component on the UK tab before pasting.

[AB33]

James,
You would not able to find components numbers on the UK sheet as you have merged cells. You have merged rows 2-26 in to one cell.
You can match(Find) each component number of sheet data analysis in a single cell(One row) ONLY in column A of UK sheet, not 24 rows as you have now.
I had spent over 3 hours this morning trying to find out why my code was not working (Not your thread).
Reason: Each cell in each sheet was merged.
I shall call today as one of those merged days.