How to Copy and paste to another worksheet if value match - Excel VBA

Question

I need to copy and paste values from sheet1 Columns(A:B) to sheet2 Columns(A:B), if Sheet1 Column F="NUMBERS"

Input

A 1 NUMBERS
B 2 TEXT
C 3 NUMBERS
D 4 TEXT
E 5 NUMBERS AND TEXT

OUTPUT
A 1
C 3

Currently i use AutoFilter and copy, paste method it sometime gives Error(Because very long Macro).

Sheets("sheet1").Select
ActiveWorkbook.save
Range("A1:J1").Select
Selection.AutoFilter
ActiveSheet.Range("$A:$J").AutoFilter Field:=6, Criteria1:="Numbers"
 Columns("A:B").Select
    Selection.Copy

 Sheets("Sheet2").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

So I need find value and copy paste method (loop based).

Solution

You can use this Macro to Copy entire Row to another Sheet has Number as value in Column F.

Option Explicit

Sub CopyRowWithSpecificText()

Dim Cell As Range

With Sheets(1)
      For Each Cell In .Range("F1:F" & .Cells(.Rows.Count, "F").End(xlUp).Row)
        If Cell.Value = "Numbers" Then

            .Rows(Cell.Row).Copy Destination:=Sheets(2).Rows(Cell.Row)
        End If
    Next Cell
End With
End Sub

---

Edited 1:

-If you don't like the Macro to produce blank rows, if any in Sheet 1 among Rows to be copied, then use this code.

    Sub CopyRowsWithNUMBER()

    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet


    Set Source = ActiveWorkbook.Worksheets("Sheet1")
    Set Target = ActiveWorkbook.Worksheets("Sheet2")

    j = 1     
    For Each c In Source.Range("F1:F20")   
        If c = "Numbers" Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           j = j + 1
        End If
    Next c
    End Sub

---

Edited 2:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngCheck As Range
    Dim CheckCell As Range
    Dim lRow As Long

    Set rngCheck = Intersect(Me.Columns("F"), Target)

    If Not rngCheck Is Nothing Then

        For Each CheckCell In rngCheck.Cells
            If CheckCell.Value = "Numbers" Then
                With Sheets("sheet2")
                    lRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
                    Me.Cells(CheckCell.Row, "A").Copy Destination:=.Cells(lRow, "A")
                    Me.Cells(CheckCell.Row, "B").Copy Destination:=.Cells(lRow, "B")

                End With
            End If
        Next CheckCell
    End If

End Sub

---

How it works:

• Copy & Paste this code in Sheet 1 as standard module.
• As soon you enter Numbers in any cells of Column F, this code will copy data from the Column A & B only for the specific Row/Rows to Sheet 2.
• Destination Column(s) should different than Source.

N.B.

• Sheet Name, Column (F) has specific text, and the specific text, Number are adjustable.
• Save the Workbook as Macro Enabled (.Xlsm).

Comments

[Rajesh Sinha]

You mean to say copy rows have NUMBER as caption is adjacent cell?

[baskar]

yeah correct, if the ColF have " Number" word then copy the ColA & B to Next sheet

[Rajesh Sinha]

Check my post the code will help U to work through loop.

[baskar]

i dont know why negative votes

[Ron Rosenfeld]

That must be one humongous macro. You could shorten your macro considerably by removing all of those unnecessary .Select, .Selection as well as references to ActiveSheet, Activate, and probably other stuff.

[baskar]

@Ron Rosenfeld as a beginner i know macro recording and one or two functions. so thats i posted here.

[Ron Rosenfeld]

To get an error because of very long macro means you have a huge macro. Especially as a beginner, you should record small segments of your project, and then make sure you understand what you have done before going on to the next. With regard to using Select, read through the thread on How to avoid using Select in Excel VBA. In addition to shortening your code, it will help you make it more robust and efficient.

[Admin]

Isn't this going to produce blank rows on the destination worksheet?

[Rajesh Sinha]

@Jeeped,, this picks only rows has value Number in col F.

[Admin]

But if Number [sic] only appears in rows 1, 3 & 5 then the destination worksheet will have the results on its own 1, 3 & 5.

[baskar]

Sorry No output , no error comes in this code (something wrong). I don't know why

[Rajesh Sinha]

@baskar,, check the value Number may be mismatched, otherwise it's working,,, nothing extraordinary to do!!

[baskar]

@RajeshS, Its working after change Sheets(1) > Sheets("Sheet1"). As you say its copy all column. Anyway to copy ColA & B only ,Also Blanks comes if the row not with value

[Rajesh Sinha]

@baskar,,, it doesn't matters whether is Sheet(1) or Sheets("Sheet1") works for both and if U need to copy few columns only edit the post & mention this in question!!

[baskar]

@RajeshS, Already mentioned in first line and my old macro code ColA &B only need to copy. I have one issue in this code if the value not match its make row as blank in output(here 2nd row blank,row 1 and 3 copied). how to rectify this

[Rajesh Sinha]

Hi @baskar,, now check my recent post Edited 2,, this code copies data only from Column A and B into destination Sheet. I'm sure this what you were looking for !

[baskar]

Private Sub > not listed in Macro Run window, Also i use call function not use. How to run this kindly guide me

[baskar]

@RajeshS, i search on internet for run these sub macro by call/run.application based method. But i dont know exactly how to trigger the macro code. Because my sheet1 Automatically filled by another macro. when i use this code i will add required place or use call function . because of private sub macro, i not able to use this last mentioned code. Sorry for the repeated questions... if you free kindly guide me. As you said last point: copy paste in sheet1 standard module is already tried but not works

[baskar]

Let us continue this discussion in chat.

[baskar]

@RajeshS I use <Edited1: Answer> then i delete Other Columns . Also I am not able to use last Private Sub Macro. Because its need to trigger by manual Dropdown selection (Also i am not able to fully understand the usage).