Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple functions, conditional functions | Excel Worksheet Functions | |||
multiple formulas and functions | Excel Discussion (Misc queries) | |||
Functions/Formulas to count multiple variables | Excel Worksheet Functions | |||
Index & Match functions - multiple criteria and multiple results | Excel Worksheet Functions | |||
functions/formulas | Excel Worksheet Functions |