Nikolette
06-19-2013, 06:05 PM
Hello all,
I'm using the macro below to search for a user-defined number (via message box) in column A, and copy/paste only the rows that begin with that value into another sheet. The only problem is that it currently is looking in all of the columns for the user-defined number and copy/pasting the rows containing it, no matter which column the number appears in. How do I tell it to only grab the rows that have the user-defined number in column A?
Sub CopyPaste042013()
Dim Number As Integer
Number = Application.InputBox("Enter the number you are looking for below", "Enter your number", , , , , , 1)
Sheet2.Cells.Delete
Application.ScreenUpdating = False
Dim xRow&, NextRow&, LastRow& NextRow = 2
LastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
For xRow = 1 To LastRow
If Application.CountIf(Rows(xRow), Number) > 0 Then
Rows(xRow).Copy Sheets("Sheet3").Rows(NextRow)
NextRow = NextRow + 1
End If
Next xRow
Application.ScreenUpdating = True
MsgBox "Macro is complete, " & NextRow - 2 & " rows containing" & vbCrLf & _
"''" & Number & "''" & " were copied to Sheet2.", 64, "Done"
End Sub
Thanks so much,
Nikolette
I'm using the macro below to search for a user-defined number (via message box) in column A, and copy/paste only the rows that begin with that value into another sheet. The only problem is that it currently is looking in all of the columns for the user-defined number and copy/pasting the rows containing it, no matter which column the number appears in. How do I tell it to only grab the rows that have the user-defined number in column A?
Sub CopyPaste042013()
Dim Number As Integer
Number = Application.InputBox("Enter the number you are looking for below", "Enter your number", , , , , , 1)
Sheet2.Cells.Delete
Application.ScreenUpdating = False
Dim xRow&, NextRow&, LastRow& NextRow = 2
LastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
For xRow = 1 To LastRow
If Application.CountIf(Rows(xRow), Number) > 0 Then
Rows(xRow).Copy Sheets("Sheet3").Rows(NextRow)
NextRow = NextRow + 1
End If
Next xRow
Application.ScreenUpdating = True
MsgBox "Macro is complete, " & NextRow - 2 & " rows containing" & vbCrLf & _
"''" & Number & "''" & " were copied to Sheet2.", 64, "Done"
End Sub
Thanks so much,
Nikolette