Copy entire row to different worksheet

[Kcope8302]

The macro that I am looking for compares column A between 2 different
worksheets(PTR and Reference data). If the Macro finds a match it takes that
row from Reference data worksheet and copies it to the PTR worksheet starting
at column X. There is information from a macro ran before this that
populates PTR. After the initial information I want the information from the
Reference data worksheet to be placed into the PTR worksheet for reporting
purposes.

Example:
Row 6 in PTR and Row 3 in Reference data match(both contain ‘CR239492’).
Once the match is found the macro takes all of row 3 from Reference data and
pastes it to Row 6 of PTR starting at column X.
Please do provide me as much assistance as possible

Thanks

-Keith-

 

[Dave Peterson]

You can't paste an entire row (A:IV) into column X:IV. The number of columns
wouldn't match. (Adjust that IV to whatever the last column is in xl2007 if
you're using that.)

But you could paste lots(?) of columns (23) A:W into X:AT (if I counted right).

Option Explicit
Sub testme()

Dim PTRWks As Worksheet
Dim RefWks As Worksheet

Dim PTRRng As Range
Dim RefRng As Range
Dim myCell As Range

Dim res As Variant 'could be an error

Dim HowManyColsToCopy As Long

HowManyColsToCopy = 23

Set PTRWks = Worksheets("PTR")
Set RefWks = Worksheets("Reference data")

With PTRWks
'row 1 of PTR has headers???
Set PTRRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
'remove any existing values in X to ???
.Range("x1").EntireColumn.Resize(, HowManyColsToCopy).ClearContents
End With

With RefWks
Set RefRng = .Range("A:A") 'use the whole column to match
End With

For Each myCell In PTRRng.Cells
If myCell.Value = "" Then
'skip it
Else
res = Application.Match(myCell.Value, RefRng, 0)
If IsError(res) Then
'no match, skip it
Else
'copies the key, too
RefRng(res).Resize(1, HowManyColsToCopy).Copy _
Destination:=PTRWks.Cells(myCell.Row, "X")
End If
End If
Next myCell

End Sub

 

[Dave Peterson]

ps.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Bernie Deitrick]

Keith,

In Cell X6, use a formula like this in X6 of PTR:

=IF(ISERROR(VLOOKUP($A6,'Reference Data'!$A$1:$Z$10000,COLUMN(B1),FALSE)),"",VLOOKUP($A6,'Reference
Data'!$A$1:$Z$10000,COLUMN(B1),FALSE))

Then copy to the right and down for as many rows and columns as you need. Change the range address
'Reference Data'!$A$1:$Z$10000 to match your data table on reference data...


HTH,
Bernie
MS Excel MVP

 

[Kcope8302]

Once a match is found I do not need the whole line from reference data to be
copied to PTR. The information in cells L:O contain the data that is relevant
for my reports. That information can then be pasted starting at cell X in
PTR.

Thanks,

 

[Kcope8302]

Hello Bernie,

I keep getting #NA in the X column. Would you be able to assist. TO further
elaborate it is just supposed to compare column A and if they match copy
L,M,N,O to the PTR worksheet.

So looking at what you provided can I copy it and have it still compare
column A between PTR and Reference Data and have it copy the relevant data.

Example: There is a match between CR94875 and it paste the relevant data
from Reference Data into PTR.

PTR .......... X Y Z AA

CR94875 TBD On-Time 5/25/09 Source List
CR94483
CR02948


Reference Data L M N O
CR94875 TBD On-Time 5/25/09 Source List