Copy entire row to different worksheet
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
Kcope8302 wrote:
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
|