Formulas for multiple functions
 

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

Formulas for multiple functions
 

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

would be a start.

--
Regards,
Tom Ogilvy

"Jerry Foley" wrote:

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

Formulas for multiple functions
 

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

"Jerry Foley" wrote:

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

Formulas for multiple functions
 

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

"Jason" wrote:

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

"Jerry Foley" wrote:

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