CODEWITHSUNDEEP
vbaexcel
Justin_DavieHigh
Justin_DavieHigh

Reputation: 225

Copy cells from one sheet to another based on criteria in another cell

I am wanting to use VBA to copy cells from one sheet to another sheet in the same workbook based on if the criteria of certain cells match in both sheets.

Example: If Sheet1!A1 = Sheet2!A1 Then Copy Sheet1!B1 To Sheet2!B2

I could do it via a function: =IF($A1=Sheet1!$A1, VLOOKUP(Sheet1!$A1, Sheet1!$A1:$D1, 2),"") but am at a loss to make it work in VBA. I thought about an IF|ELSE Statement but couldn't get it to work.

Upvotes: 1

Views: 6333

Answers (2)

hedgedandlevered
hedgedandlevered

Reputation: 2394

Sub copyToSheet()
For i = 1 To 10
    If ThisWorkbook.Worksheets("sheet1").Range("A1").Offset(i - 1, 0).Value = ThisWorkbook.Worksheets("sheet2").Range("A1").Offset(i - 1, 0).Value Then
    ThisWorkbook.Worksheets("sheet2").Range("A1").Offset(i - 1, 1) = ThisWorkbook.Worksheets("sheet1").Range("A1").Offset(i - 1, 0).Value
    End If
    Next i
End Sub

Upvotes: 0

user184994
user184994

Reputation: 18301

This should work for you:

For Counter = 1 To 10 
    If Sheets(1).Range("A" & Counter).Value = Sheets(2).Range("A" & Counter).Value Then
        Sheets(2).Range("B" & (Counter + 1)).Value = Sheets(1).Range("B" & Counter).Value
    End If
Next Counter

Upvotes: 0

Related Questions