CODEWITHSUNDEEP
vbaexcel
Hrey
Hrey

Reputation: 31

Need Help In EXCEL VBA match and copy

I am working in one of the Excel sheet and i am new in VBA. I have a situation where i have 2 different sheets ("OriginalData1") and ("VBAFilter"). I have one cell (F3) in ("VBAFilter") sheet and it has a company name, which appear in "OriginalData1" multiple times. I want to find the same company name in ("OriginalData1") sheet. If it appears 4 times in "OriginalData1" I need to transfer in "VBAFilter" same 4 times and if it is a match then copy the entire row and paste it in sheet("VBAFilter") in cell range (F3:O2000").

Here's the code i have:

Sub datafind()

Dim CompanyName As String
Dim finalrow As Integer
Dim i As Integer

Worksheets("VBAFilter").Range("F9:O1000").ClearContents
CompanyName = Sheets("VBAFilter").Range("F3").Value
finalrow = Sheets("OriginalData1").Range("B" & Rows.Count).End(xlUp).Row


For i = 2 To finalrow

 If Worksheets("OriginalData1").Cells(i, 2) = CompanyName Then
    Worksheets("OriginalData1").Range("B" & Rows.Count).Copy
    Worksheets("VBAFilter").Select
    Range("F1000").End(xlUp).Offset(1).PasteSpecial xlPasteFormulasAndNumberFormats
  End If
Next i

End Sub

Upvotes: 0

Views: 165

Answers (1)

Siphor
Siphor

Reputation: 2544

Sub datafind()

Dim CompanyName As String
Dim finalrow As Integer
Dim i As Integer
Dim j As Integer

j= 9

Worksheets("VBAFilter").Range("F9:O1000").ClearContents
CompanyName = Sheets("VBAFilter").Range("F3").Value
finalrow = Sheets("OriginalData1").Range("B" & Rows.Count).End(xlUp).Row


For i = 2 To finalrow

    If Worksheets("OriginalData1").Cells(i, 2) = CompanyName Then
        Worksheets("OriginalData1").Cells(i, 2).Resize(1,1000).Copy
        Worksheets("OriginalData1").Cells(j, "F").Resize(1,1000).PasteSpecial xlPasteFormulasAndNumberFormats
        j = j + 1
    End If
Next i

End Sub

Upvotes: 1

Related Questions