Macro for copying and replacing data

Closed
simrob316 - Aug 17, 2011 at 10:20 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Aug 17, 2011 at 11:33 PM
Hello,

I'm looking for the best way, which I am assuming is a macro to copy data from one spredsheet to another. The macro must copy all data from one spreadsheet (sheet2) row by row to another spreadsheet (sheet1). It must copy based on a value in column A in sheet 2 then look for the same value in column A sheet 1 and replace it with the copied row (the whole row must be copied and replaced)

appreciate any help


Related:

1 response

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Aug 17, 2011 at 11:33 PM
not clear

"replace it with the copied row "

sheet1 is to be replaced by sheet 2 or sheet 2 is to be replaced by sheet1????

try this macro and modify if necessary

Sub test()
Dim r As Range, c As Range, r1 As Range, x As String
With Worksheets("sheet1")
Set r = Range(.Range("A2"), .Range("A2").End(xlDown))
For Each c In r
x = c.Value
With Worksheets("sheet2").Columns("A:A")
Set r1 = .Cells.Find(what:=x, lookat:=xlWhole)
If Not r1 Is Nothing Then
r1.EntireRow.Copy
End If
End With
c.PasteSpecial
Next c
End With


End Sub
0