VBA copy rows that meet criteria to another sheet

Question

I am new to VBA   I want to copy a row from Sheet2 to Sheet1 if the first cell in this row says X and then do so for all the rows that meet this criteria  I have an error in the If condition   I don t know how to fix it   Sub LastRowInOneColumn    Find the last used row in a Column  column A in this example     Worksheets  Sheet2   Activate     Dim LastRow As Long     With ActiveSheet         LastRow    Cells  Rows Count   A   End xlUp  Row     End With     MsgBox  LastRow      For i   1 To LastRow     If Worksheet Cells i  1  Value    X  Then     ActiveSheet Row Value Copy       Destination  Hoja1     End If     Next i  End Sub

User · Answer

After formatting the previous answer to my own code, I have found an efficient way to copy all necessary data if you are attempting to paste the values returned via AutoFilter to a separate sheet.

With .Range("A1:A" & LastRow)
    .Autofilter Field:=1, Criteria1:="=*" & strSearch & "*"
    .Offset(1,0).SpecialCells(xlCellTypeVisible).Cells.Copy
    Sheets("Sheet2").activate
    DestinationRange.PasteSpecial
End With

In this block, the AutoFilter finds all of the rows that contain the value of strSearch and filters out all of the other values. It then copies the cells (using offset in case there is a header), opens the destination sheet and pastes the values to the specified range on the destination sheet.

User · Answer

You need to specify workseet  Change line   If Worksheet Cells i  1  Value    X  Then   to   If Worksheets  Sheet2   Cells i  1  Value    X  Then   UPD   Try to use following code  but it s not the best approach  As  SiddharthRout suggested  consider about using Autofilter    Sub LastRowInOneColumn      Dim LastRow As Long    Dim i As Long  j As Long      Find the last used row in a Column  column A in this example    With Worksheets  Sheet2         LastRow    Cells  Rows Count   A   End xlUp  Row    End With     MsgBox  LastRow      first row number where you need to paste values in Sheet1     With Worksheets  Sheet1         j    Cells  Rows Count   A   End xlUp  Row   1    End With      For i   1 To LastRow        With Worksheets  Sheet2              If  Cells i  1  Value    X  Then                 Rows i  Copy Destination  Worksheets  Sheet1   Range  A   amp  j                 j   j   1            End If        End With    Next i End Sub

[excel] VBA copy rows that meet criteria to another sheet

Examples related to excel

Examples related to copy-paste

Examples related to vba