Report

A macro to copy/paste a cell and other cells in the same row [Solved]

Ask a question tanja20 - Last answered on May 9, 2017 at 11:12 AM by TrowaD
I have two wooksheets in a workbook. I need that a value of each cell in a range C3:C52 in sheet 2002 is being searched for in the range B2:B101 of the sheet Rawdata. When found, the value of the cell in the range B2:B101 of the sheet Rawdata and the next cell in the same row (or a complete row with cells that are not empty) must be copied and pasted in the sheet 2002 next to the cell which value was searched for.

I have modified code which I found in the Internet. But with modifications it doesn't work.

Sub Find_Copy_Paste()

Dim sh1 As Worksheet, sh2 As Worksheet
Dim rng1 As Range, rng2 As Range
Dim cell As Range, FoundCells As Range
Dim row As Range
Set sh1 = Worksheets("2002")
Set sh2 = Worksheets("Rawdata")
Set rng1 = sh1.Range("C3:C52")
Set rng2 = sh2.Range("B2:B101")
For Each cell In rng1
If Not IsEmpty(cell) Then
Set FoundCells = rng2.Find(cell.Value)
If Not FoundCells Is Nothing Then
' Copy Found cell to one column on right of cell being searched for
FoundCells.EntireRow.Copy Destination:=cell.Offset(0, 1)
End If
End If
Next
Set rng1 = Nothing
Set rng2 = Nothing
Set sh1 = Nothing
Set sh2 = Nothing

End Sub
See more 
Helpful
+0
plus moins
Hi Tanja,

You can't paste an entire row in column B, it won't fit. You will need to adjust:
FoundCells.EntireRow.Copy Destination:=cell.Offset(0, 1)
into maybe:
range(cells(foundcells.row,"B"),cells(foundcells.row,"C")).copy cell.offset(0,1)

Best regards,
Trowa
tanja20- May 9, 2017 at 11:05 AM
Hi Trowa,

thank you very much for your tip. It works now!

Best regards,
Tanja
Reply
TrowaD 2238Posts Sunday September 12, 2010Registration date ModeratorStatus June 26, 2017 Last seen - May 9, 2017 at 11:12 AM
Awesome! Thanks for the feedback.
Reply
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!