Formulas for multiple functions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jerry Foley - 19 Feb 2007 21:54 GMT

I have a rather large multisheet workbook. I want to write a formula or macro
that will search the "A" column in each worksheet. If there is a number in
the column I want to copy the entire row to a different worksheet. This is
above my head and any support will be appreciated.

Jerry

Reply to this Message

Tom Ogilvy - 19 Feb 2007 22:03 GMT

http://www.rondebruin.nl/copy5.htm

would be a start.

Signature

Regards,
Tom Ogilvy

> I have a rather large multisheet workbook. I want to write a formula or macro
> that will search the "A" column in each worksheet. If there is a number in
> the column I want to copy the entire row to a different worksheet. This is
> above my head and any support will be appreciated.
>
> Jerry

Reply to this Message

Jason - 19 Feb 2007 22:18 GMT

Here is one way.

The following assumes that you will search all worksheets except for Sheet2,
since this is the sheet where the rows will be pasted. So be sure to change
the name of "Sheet2" to the sheet to which you would like to paste.

Sub numbers()

Dim ws As Worksheet, cell As Range, rng As Range

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = "Sheet2" Then
For Each cell In ws.Range("A1:A" & ws.Range("A65536").End(xlUp).Row)
If IsNumeric(cell) = True Then
cell.EntireRow.Copy _
Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0)
End If
Next cell
End If
Next ws

End Sub

HTH,
Jason

Reply to this Message

Jerry Foley - 19 Feb 2007 23:33 GMT

Thanks for the help. Pardon my ignorance but how do I get this actually intot
he spreadsheet?
Jerry

> Here is one way.
>
[quoted text clipped - 28 lines]
> >
> > Jerry

Reply to this Message