VBA copy rows that meet criteria to another sheet

by

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" & j)
               j = j + 1
           End If
       End With
   Next i
End Sub

More Related Contents:

  1. Copy Paste Values only( xlPasteValues )
  2. Copy/Paste/Calculate Visible Cells from One Column of a Filtered Table
  3. Get text from clipboard using GetText – avoid error on empty clipboard
  4. Excel macro – paste only non empty cells from one sheet to another
  5. VBA comma separated cells, needt last entry only
  6. How can I find last row that contains data in a specific column?
  7. Open Excel file for reading with VBA without display
  8. Wait for shell command to complete [duplicate]
  9. How to return the number of dimensions of a (Variant) variable passed to it in VBA [duplicate]
  10. How to add a named sheet at the end of all Excel sheets?
  11. Find last used cell in Excel VBA
  12. VBA general way for pulling data out of SAP [closed]
  13. Does Dir() make any guarantee on the order of files returned?
  14. Call a Sub with a String
  15. Delete Row based on Search Key VBA
  16. How to add a button programmatically in VBA next to some sheet cell data?
  17. How to add parameters to an external data query in Excel which can’t be displayed graphically?
  18. ADODB Connection String for .csv
  19. Pivotfields multiple filter
  20. How to send mail when the .Send does not work?
  21. How to clear memory to prevent “out of memory error” in VBA?
  22. Extract Data from PDF and Add to Worksheet
  23. How do I click a link on a web page using Excel VBA?
  24. EXCEL VBA Check if entry is empty or not ‘space’
  25. Ping IP Address with VBA code and return results in Excel
  26. Create hyperlink to another sheet
  27. Trying to open the workbook in separate instance
  28. Convert Early Binding VBA to Late Binding VBA : Excel to Outlook Contacts
  29. Delete all data rows from an Excel table (apart from the first)
  30. Excel VBA Run-time Error ‘32809’ – Trying to Understand it

Leave a Comment