Problem with code
 

I have a workbook with 9 worksheets and 2 charts. I am o.k. with exluding
the worksheets and charts. The problem with code come in the line

"If Abs(cell.Offset(0, i).Value) cell.Parent.Cells(7, i + 1).Value Then".

You guys are pretty smart guys and can probably see what the code does.
But in short, it copies rows from the required worksheets that meet a
criteria, based on row 7 on the "Greater than" workheet, and then pasts them
in the Greater than sheet starting at row 9. Are you guys able to figure out
why this code will not work anymore?

Option Explicit

Sub PostGreaterThan()

Dim shGreat As Worksheet
Dim wsh As Worksheet
Dim cell As Range
Dim rID As Range
Dim i As Long
Dim rDest As Range

Set shGreat = ThisWorkbook.Worksheets("greater than")
shGreat.Range("A9:IV65536").Clear

For Each wsh In ThisWorkbook.Worksheets
If wsh.Name < shGreat.Name And wsh.Name < "Company & Fleet Totals"
Then
Set rID = wsh.Range("a9", wsh.Range("A" &
wsh.Rows.Count).End(xlUp))

For Each cell In rID.Cells
If Not IsEmpty(cell) Then
For i = 4 To 11
If Abs(cell.Offset(0, i).Value)
cell.Parent.Cells(7, i + 1).Value Then
Set rDest = shGreat.Range("A" &
shGreat.Rows.Count).End(xlUp).Offset(1, 0)
cell.EntireRow.Copy rDest
Exit For
End If
Next i
End If
Next cell
End If
Next wsh

Thanks,
Steve

Problem with code
 

How exactly is it not working? Are you getting Type Mismatch for text in the
cells?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steve" wrote in message
...
I have a workbook with 9 worksheets and 2 charts. I am o.k. with
exluding
the worksheets and charts. The problem with code come in the line

"If Abs(cell.Offset(0, i).Value) cell.Parent.Cells(7, i + 1).Value
Then".

You guys are pretty smart guys and can probably see what the code does.
But in short, it copies rows from the required worksheets that meet a
criteria, based on row 7 on the "Greater than" workheet, and then pasts
them
in the Greater than sheet starting at row 9. Are you guys able to figure
out
why this code will not work anymore?

Option Explicit

Sub PostGreaterThan()

Dim shGreat As Worksheet
Dim wsh As Worksheet
Dim cell As Range
Dim rID As Range
Dim i As Long
Dim rDest As Range

Set shGreat = ThisWorkbook.Worksheets("greater than")
shGreat.Range("A9:IV65536").Clear

For Each wsh In ThisWorkbook.Worksheets
If wsh.Name < shGreat.Name And wsh.Name < "Company & Fleet
Totals"
Then
Set rID = wsh.Range("a9", wsh.Range("A" &
wsh.Rows.Count).End(xlUp))

For Each cell In rID.Cells
If Not IsEmpty(cell) Then
For i = 4 To 11
If Abs(cell.Offset(0, i).Value)
cell.Parent.Cells(7, i + 1).Value Then
Set rDest = shGreat.Range("A" &
shGreat.Rows.Count).End(xlUp).Offset(1, 0)
cell.EntireRow.Copy rDest
Exit For
End If
Next i
End If
Next cell
End If
Next wsh

Thanks,
Steve

Problem with code
 

It is copying and pasting everything from the other worksheets. Instead of
jus the rows that meet the criteria.

"Bob Phillips" wrote:

How exactly is it not working? Are you getting Type Mismatch for text in the
cells?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steve" wrote in message
...
I have a workbook with 9 worksheets and 2 charts. I am o.k. with
exluding
the worksheets and charts. The problem with code come in the line

"If Abs(cell.Offset(0, i).Value) cell.Parent.Cells(7, i + 1).Value
Then".

You guys are pretty smart guys and can probably see what the code does.
But in short, it copies rows from the required worksheets that meet a
criteria, based on row 7 on the "Greater than" workheet, and then pasts
them
in the Greater than sheet starting at row 9. Are you guys able to figure
out
why this code will not work anymore?

Option Explicit

Sub PostGreaterThan()

Dim shGreat As Worksheet
Dim wsh As Worksheet
Dim cell As Range
Dim rID As Range
Dim i As Long
Dim rDest As Range

Set shGreat = ThisWorkbook.Worksheets("greater than")
shGreat.Range("A9:IV65536").Clear

For Each wsh In ThisWorkbook.Worksheets
If wsh.Name < shGreat.Name And wsh.Name < "Company & Fleet
Totals"
Then
Set rID = wsh.Range("a9", wsh.Range("A" &
wsh.Rows.Count).End(xlUp))

For Each cell In rID.Cells
If Not IsEmpty(cell) Then
For i = 4 To 11
If Abs(cell.Offset(0, i).Value)
cell.Parent.Cells(7, i + 1).Value Then
Set rDest = shGreat.Range("A" &
shGreat.Rows.Count).End(xlUp).Offset(1, 0)
cell.EntireRow.Copy rDest
Exit For
End If
Next i
End If
Next cell
End If
Next wsh

Thanks,
Steve