for the following macro, I want it to seach column 'c' in all sheets except
sheet4. If there is a numeric value in col 'c' of any of the worksheets in
the workbook, I want to copy the entire row to sheet 4. The code I have
written will also copy rows of data that fall between the rows that have data
in col 'c'. Help...
Sub numbers()

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

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = "Sheet4" Then
For Each cell In ws.Range("c1:c" & ws.Range("c65536").End(xlUp).Row)
If IsNumeric(cell.Offset(0, 2).Value) Then
cell.EntireRow.Copy _
Sheets("Sheet4").Range("A65536").End(xlUp).Offset( 1, 0)
End If
Next cell
End If
Next ws

End Sub

Try to stay in the same thread if possible...

My suggestions were either or : you have included both, so now you're checking Column E (C, then 2 to the right).

--
Tim Williams
Palo Alto, CA


"Jerry Foley" wrote in message ...
for the following macro, I want it to seach column 'c' in all sheets except
sheet4. If there is a numeric value in col 'c' of any of the worksheets in
the workbook, I want to copy the entire row to sheet 4. The code I have
written will also copy rows of data that fall between the rows that have data
in col 'c'. Help...
Sub numbers()

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

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = "Sheet4" Then
For Each cell In ws.Range("c1:c" & ws.Range("c65536").End(xlUp).Row)
If IsNumeric(cell.Offset(0, 2).Value) Then
cell.EntireRow.Copy _
Sheets("Sheet4").Range("A65536").End(xlUp).Offset( 1, 0)
End If
Next cell
End If
Next ws

End Sub

Thnaks Tim, I am confused however...what exact changes shoul dbe made to the
code in the thread to get my desired results? C is the only column I want to
seach in each sheet. Thanks

"Tim Williams" wrote:

Try to stay in the same thread if possible...

My suggestions were either or : you have included both, so now you're checking Column E (C, then 2 to the right).

--
Tim Williams
Palo Alto, CA


"Jerry Foley" wrote in message ...
for the following macro, I want it to seach column 'c' in all sheets except
sheet4. If there is a numeric value in col 'c' of any of the worksheets in
the workbook, I want to copy the entire row to sheet 4. The code I have
written will also copy rows of data that fall between the rows that have data
in col 'c'. Help...
Sub numbers()

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

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = "Sheet4" Then
For Each cell In ws.Range("c1:c" & ws.Range("c65536").End(xlUp).Row)
If IsNumeric(cell.Offset(0, 2).Value) Then
cell.EntireRow.Copy _
Sheets("Sheet4").Range("A65536").End(xlUp).Offset( 1, 0)
End If
Next cell
End If
Next ws

End Sub

This line:

If IsNumeric(cell.Offset(0, 2).Value) Then
checks to see if the cell 2 columns to the right of column C is numeric.

Maybe just:
If IsNumeric(cell.Value) Then
(to stay in column C)

But be careful. An empty cell will be treated as numeric by excel.

If that's not ok, you could use:
If application.isnumber(cell.Value) Then

or check a couple of things:

if isempty(cell.value) then
'don't do it
elseIf IsNumeric(cell.Value) Then
'do the copy
end if




Jerry Foley wrote:

Thnaks Tim, I am confused however...what exact changes shoul dbe made to the
code in the thread to get my desired results? C is the only column I want to
seach in each sheet. Thanks

"Tim Williams" wrote:

Try to stay in the same thread if possible...

My suggestions were either or : you have included both, so now you're checking Column E (C, then 2 to the right).

--
Tim Williams
Palo Alto, CA


"Jerry Foley" wrote in message ...
for the following macro, I want it to seach column 'c' in all sheets except
sheet4. If there is a numeric value in col 'c' of any of the worksheets in
the workbook, I want to copy the entire row to sheet 4. The code I have
written will also copy rows of data that fall between the rows that have data
in col 'c'. Help...
Sub numbers()

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

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = "Sheet4" Then
For Each cell In ws.Range("c1:c" & ws.Range("c65536").End(xlUp).Row)
If IsNumeric(cell.Offset(0, 2).Value) Then
cell.EntireRow.Copy _
Sheets("Sheet4").Range("A65536").End(xlUp).Offset( 1, 0)
End If
Next cell
End If
Next ws

End Sub




--

Dave Peterson

It appears that you are NOT asking for col C.

--
Don Guillett
SalesAid Software

"Jerry Foley" wrote in message
...
for the following macro, I want it to seach column 'c' in all sheets
except
sheet4. If there is a numeric value in col 'c' of any of the worksheets in
the workbook, I want to copy the entire row to sheet 4. The code I have
written will also copy rows of data that fall between the rows that have
data
in col 'c'. Help...
Sub numbers()

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

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = "Sheet4" Then
For Each cell In ws.Range("c1:c" &
ws.Range("c65536").End(xlUp).Row)
If IsNumeric(cell.Offset(0, 2).Value) Then
cell.EntireRow.Copy _
Sheets("Sheet4").Range("A65536").End(xlUp).Offset( 1,
0)
End If
Next cell
End If
Next ws

End Sub

Sub numbers()

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

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = "Sheet4" Then

Set rng = ws.Range("C1:C" & ws.Range("C65536").End(xlUp).Row)

For Each cell In rng
If cell.Value < "" and IsNumeric(cell.Value) Then
cell.EntireRow.Copy _
Sheets("Sheet4").Range("A65536").End(xlUp).Offset( 1, 0)
End If
Next cell
End If
Next ws

End Sub


--
Tim Williams
Palo Alto, CA


"Jerry Foley" wrote in message ...
Thnaks Tim, I am confused however...what exact changes shoul dbe made to the
code in the thread to get my desired results? C is the only column I want to
seach in each sheet. Thanks

"Tim Williams" wrote:

Try to stay in the same thread if possible...

My suggestions were either or : you have included both, so now you're checking Column E (C, then 2 to the right).

--
Tim Williams
Palo Alto, CA


"Jerry Foley" wrote in message ...
for the following macro, I want it to seach column 'c' in all sheets except
sheet4. If there is a numeric value in col 'c' of any of the worksheets in
the workbook, I want to copy the entire row to sheet 4. The code I have
written will also copy rows of data that fall between the rows that have data
in col 'c'. Help...
Sub numbers()

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

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = "Sheet4" Then
For Each cell In ws.Range("c1:c" & ws.Range("c65536").End(xlUp).Row)
If IsNumeric(cell.Offset(0, 2).Value) Then
cell.EntireRow.Copy _
Sheets("Sheet4").Range("A65536").End(xlUp).Offset( 1, 0)
End If
Next cell
End If
Next ws

End Sub