select, copy, and paste multiple different rows to new sheet

I need to select all rows that have an "x" in column B. Then I need to paste those rows to a new sheet. I don't know much about VBA, so this is as far as I could get.

Sub Macro4()

'Define last row
Dim lastrow As Integer
lastrow = Range("C" & Rows.Count).End(xlUp).Row


'Run loop
For i = 1 To lastrow

If Sheets("Sheet1").Cells(i, 2).Value = "x" Then
Sheets("Sheet1").Cells(i, 2).EntireRow.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
End If

Next i

End Sub


I also tried this:

Sub Macro5()
Dim text As String
text = "x"

For Each cell In Sheets("Sheet1").Columns("B:B")
If ActiveCell = text Then
EntireRow.Copy
Sheets("Sheet2").Range("A1").Select
Selection.Paste
End If
Next cell

End Sub

not sure you really want this, but try :

Please Login or Register  to view this content.

Please use code tags with your code as per forum's rule.

Please Login or Register  to view this content.

Hi prjames3,

Use this code, it may help you

Sub CopyNonBlanks()
Dim EndRow As Integer
Application.ScreenUpdating = False
With Sheet3
.AutoFilterMode = False
EndRow = .Range("H" & Rows.Count).End(xlUp).Row
.Range("H1:H" & EndRow ).AutoFilter Field:=1, Criteria1:="<>"
.Range("H1:H" & EndRow ).Copy Destination:=Sheet2.Range("A1")
.Range("H1").AutoFilter
End With
Application.ScreenUpdating = True
End Sub

Ankit,
You are also not using code tags. Please take a moment to read forum's rule.

Thanks for the help. I'll remember to use code tags next time. AB33, when I run your code it looks like all the rows are being copied, but the result on sheet 2 is only the last row with an "x" in column b from sheet 1 pasted in row 2.

Actually I just fixed it. Here's the code:

Please Login or Register  to view this content.

prjames3,
They are not my code, I only adjusted your codes. You have two codes and both should work, unless the "x" is upper case.

This is my code.

Please Login or Register  to view this content.