VBA problems with copy and pasting

[timhue88]

Hi all,

I currently have an issue with regard to VBA

Right now, i have 2 sheets.

Sheet A & Sheet B

In sheet A i got these values


123USD123
234USD234
345USD345
456USD456


In sheet B i have these values

234USD234 18 19 20 21


I would like sheet A to become this

123USD123
234USD234 18 19 20 21
345USD345
456USD456

However, i am required to delete the contents in sheet B and add in the particulars of another account maybe 345USD345.

Then sheet B becomse


345USD345 1 2 3 4

then when i run my macro i require sheet A to become


123USD123
234USD234 18 19 20 21
345USD345 1 2 3 4
456USD456

Thus, it retains the value of 234usd234 that was done just now. please advice on how to do this.

 

[Ralajer]

The below when run will look at SheetB for the value in the specified range for example B2 and find it in column A of SheetA and copy the cells from sheetB to the row it was found on sheetA.

Change the sheet names and ranges to match your sheets.

Sub transferData()
    Dim searchRange As Range
    Dim whatRange As Range
    Dim whatVal As String
    Dim foundRow As Long

    '// Range to be searched. Column with items
    Set searchRange = Sheets("SheetA").Range("A:A")
    '// Range containing the cells to be copied include Look name
    Set whatRange = Sheets("SheetB").Range("B2:F2")
    
    whatVal = whatRange(1, 1).Value
    
    foundRow = 0
    On Error Resume Next
    
    foundRow = searchRange.Find(What:=whatVal, _
                    after:=searchRange.Cells(1), _
                    Lookat:=xlWhole, _
                    LookIn:=xlValues, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False).Row
    On Error GoTo 0
    If foundRow = 0 Then
        MsgBox whatVal & " — Wasn't Found!"
        Exit Sub
    End If
    
    whatRange.Copy searchRange(foundRow, 1)
    whatRange.ClearContents
End Sub

 

[timhue88]

yes! you solved it. thank you so much :D