I need help with a macro to compare data between worksheets
 

Hello

I have a workbook with 8 worksheets: Menu, Raw Data, Filtered Data, Ignore,
Add, Review, In Progress, Completed.

I want to design a macro to compare data between some of these worksheets,
but my limited programming knowledge is working against me.

Each worksheet has, as its first column (i.e. column A), a unique identifier
called "AGS". I want to use this field to conduct the comparison of rows
between the worksheets.

What I am trying to achieve, in the following order, is:

Compare the data between the "Filtered Data" worksheet with that in the
"Completed" worksheet.

- IF THE DATA IS PRESENT, I want the macro to subtract the date in
Column W of the "Completed" worksheet from today's date and if the value is
greater than 365 I want the corresponding row (i.e. from the Filtered Data
worksheet) copied into the "Review" worksheet. If the value is 365 or less, I
want the corresponding row (i.e. from the Filtered Data worksheet) copied
into the "Ignore" worksheet.

- IF THE DATA IS NOT PRESENT, I then want the macro to compare the
data with the "In Progress" worksheet instead. If this data IS present, then
I want the corresponding row (i.e. from the Filtered Data worksheet) copied
into the "Ignore" worksheet. If the data is not present, I want the
corresponding row (i.e. from the Filtered Data worksheet) copied into the
"Add" worksheet.

Below is the code I started to work with, but now I have no idea! I think
the date comparison is wrong, but dont know enough to know what else may be
wrong.



Sub CompareData()

Sheets("Ignore").Columns("A:Z").Delete
Sheets("Add").Columns("A:Z").Delete
Sheets("Review").Columns("A:Z").Delete

FilteredRowCount = 1
InProgressRowCount = 1
ReviewRowCount = 1
IgnoreRowCount = 1

With Sheets("Filtered Data")

Do While .Range("A" & FilteredRowCount) < ""

Ignore = False
SearchItem = .Range("A" & FilteredRowCount)

With Sheets("Completed")
Set c = .Columns("A:A").Find(What:=SearchItem, _
LookIn:=xlValues)
End With

If c Is Nothing Then

With Sheets("In Progress")
Set c = .Columns("A:A").Find(What:=SearchItem, _
LookIn:=xlValues)
End With

If c Is Nothing Then
.Rows(FilteredRowCount).Copy _
Destination:=Sheets("Add").Rows(InProgressRowCount )
InProgressRowCount = InProgressRowCount + 1
Else
'compare dates

If IsDate(.Range("W" & FilteredRowCount)) = True And _
IsDate(c.Offset(0, 22)) = True Then

If CDate(.Range("K" & FilteredRowCount)) 365 Then

.Rows(FilteredRowCount).Copy _
Destination:=Sheets("Review").Rows(ReviewRowCount)
ReviewRowCount = ReviewRowCount + 1

else
Ignore = True
End If
else
Ignore = true
End If
End If

Else
ignore = true
End If

if ignore = true then
.Rows(FilteredRowCount).Copy _
Destination:=Sheets("Ignored").Rows(IgnoredRowCoun t)
IgnoreRowCount = IgnoreRowCount + 1
end if
FilteredRowCount = FilteredRowCount + 1
Loop

End With

MsgBox ("New data has been successfully compared to existing data.")

End Sub


Any help would be GREATLY appreciated. I will not be offended if you feel
the need to rewrite this macro from scratch!

Joe.
--
If you can measure it, you can improve it!