I have a workbook with two sheets. Sheet one has customer information with
account numbers in column A. I need a macro that will allow me to search for
an account number. When it finds the account number, it will cut the entire
row and paste it into the next empty row in sheet 2 in the workbook. All
methods I have tried have failed so any help is appreciated. I am using
Excel 2007. Thanks in advance.

Execute while on the source sheet.

Sub findandcopy()
what = 33 'youracctnum
mr = Columns("A").Find(what, After:=Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext).Row

With Sheets("sheet2")
dlr = .Cells(Rows.Count, "A").End(xlUp).Row + 1
Rows(mr).Copy .Rows(dlr)
End With

End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"brodiemac" wrote in message
...
I have a workbook with two sheets. Sheet one has customer information with
account numbers in column A. I need a macro that will allow me to search
for
an account number. When it finds the account number, it will cut the
entire
row and paste it into the next empty row in sheet 2 in the workbook. All
methods I have tried have failed so any help is appreciated. I am using
Excel 2007. Thanks in advance.

I get the error Object variable or With block not set.

"Don Guillett" wrote:

Execute while on the source sheet.

Sub findandcopy()
what = 33 'youracctnum
mr = Columns("A").Find(what, After:=Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext).Row

With Sheets("sheet2")
dlr = .Cells(Rows.Count, "A").End(xlUp).Row + 1
Rows(mr).Copy .Rows(dlr)
End With

End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"brodiemac" wrote in message
...
I have a workbook with two sheets. Sheet one has customer information with
account numbers in column A. I need a macro that will allow me to search
for
an account number. When it finds the account number, it will cut the
entire
row and paste it into the next empty row in sheet 2 in the workbook. All
methods I have tried have failed so any help is appreciated. I am using
Excel 2007. Thanks in advance.

If you like, send your workbook to my address below

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"brodiemac" wrote in message
...
I get the error Object variable or With block not set.

"Don Guillett" wrote:

Execute while on the source sheet.

Sub findandcopy()
what = 33 'youracctnum
mr = Columns("A").Find(what, After:=Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext).Row

With Sheets("sheet2")
dlr = .Cells(Rows.Count, "A").End(xlUp).Row + 1
Rows(mr).Copy .Rows(dlr)
End With

End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"brodiemac" wrote in message
...
I have a workbook with two sheets. Sheet one has customer information
with
account numbers in column A. I need a macro that will allow me to
search
for
an account number. When it finds the account number, it will cut the
entire
row and paste it into the next empty row in sheet 2 in the workbook.
All
methods I have tried have failed so any help is appreciated. I am
using
Excel 2007. Thanks in advance.