Looking for a macro to do a match and multiple copy.

**Thadd** · 04-09-2012, 11:48 PM

Please assist me:
I have 2 sheets: "Hours" and "Reader" I need to locate a matching row from A2 in "Reader" to Column A in "Hours".
Then copy the contents of column B in "Reader" to the 5th column in "Hours"; copy the contents of column D in "Reader" to the 7th column in "Hours"; and copy the contents of column E in "Reader" to the 8th column in "Hours".

This should continue untill there is no data in the column (end of the list).

I have to go with the numbered columns B/C they hide the columns as the days pass and the column letters change.

I hope this is possible, otherwise I'll have to build in "expand all columns" and somehow search for the date in the header also... One thing at a time

Thanks, Thadd.

**jraj1106** · 04-10-2012, 01:13 AM

Hi
Can you provide a sample data?

**jraj1106** · 04-10-2012, 01:27 AM

Hi
Try this code. I have not tested it but written based on your description.

Sub COPYDATA()
Dim rcntreader As Long, rcnthours As Long

rcntreader = Sheets("Reader").Range("A" & Rows.Count).End(xlUp).Row
rcnthours = Sheets("Hours").Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To rcntreader
For j = 2 To rcnthours
If Sheets("Reader").Range("A" & i).Value = Sheets("Hours").Range("A" & j).Value Then
Sheets("Reader").Range("B" & i).Copy (Sheets("Hours").Range("E" & j))
Sheets("Reader").Range("D" & i).Copy (Sheets("Hours").Range("G" & j))
Sheets("Reader").Range("E" & i).Copy (Sheets("Hours").Range("H" & j))
End If
Next
Next
End Sub

Regards..

**Thadd** · 04-10-2012, 10:49 PM

Thanks jraj1106....
I finally had time to try your code and it does find the matching column in the Hours sheet, however it copies the data to columns E, G, and H. Since I have hidden the columns D through U, it possible to skip hidden columns, and paste the data to the 5th, 7th and 8th visible columns?

Again, thanks for your help on this.

**jraj1106** · 04-11-2012, 02:11 AM

Hi
Try this:

Sub COPYDATA()
Dim rcntreader As Long, rcnthours As Long

rcntreader = Sheets("Reader").Range("A" & Rows.Count).End(xlUp).Row
rcnthours = Sheets("Hours").Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To rcntreader
For j = 2 To rcnthours
If Sheets("Reader").Range("A" & i).Value = Sheets("Hours").Range("A" & j).Value Then
Sheets("Reader").Range("B" & i).Copy (Sheets("Hours").Range("V" & j))
Sheets("Reader").Range("D" & i).Copy (Sheets("Hours").Range("W" & j))
Sheets("Reader").Range("E" & i).Copy (Sheets("Hours").Range("X" & j))
End If
Next
Next
End Sub

**Thadd** · 04-11-2012, 08:36 PM

Thank you very much for the response, I see that you modified the script to paste data to columns V,W and X. While this will work, Each day I will need to modify the script to maychthat day's columns.

Perhaps a simpler approach will work- adding a column (in "Hours") match for the date contained in "Reader" column C.

With a few corrections, here is an idea of what I'm requesting:

search sheet "Hours", row 2 for matching date MM/DD/YYYY in column C in Reader =X(matching Column in "Hours")

Search column A in "Hours" for matching number in column A in "Reader"
=Y(matching row in "Hours")

paste data from column B, in "Reader" to row Y, column X-5 in "hours"

paste data from column D, in "Reader" to row Y, column X-3 in "hours"

paste data from column E, in "Reader" to row Y, column X-2 in "hours"

This should continue until all populated rows in "Reader" have been checked.

Note:
Date data in Column C sheet "Reader" and row 2 sheet "Hours" is "4/13/2012", but displays as "13-Apr" in the "Hours" sheet.
This is just a formatting issue, but I don't know if it will affect the search.

I think this should do the trick for me.

Thanks again for your help

Thadd.