Help

[Tripla]

I have 3 worksheets in my Workbook. Each sheet is from 3 separate
years AA05,AA06,AA07 and named accordingly. I have this code to check
each Column B of all the worksheets for matches and I was hoping to
return the results of the unmatched to a new Output sheet. Can anyone
help?
Ex:
Worksheet AA05
A B
1 Banks, Tom
2 Hanks,John
3 Johnson, Rob
Worksheet AA06
A B
1 Banks, Tom
2 Hawton,John
3 Johnthorp, Rob
WorksheetAA07
A B
1 Banks, Tom
2 Hawton,John
3 Johnson, Rob

Sub ()
Dim sht As Worksheet
Dim lrow As Long
Dim dest_lrow As Long
For Each sht In ActiveWorkbook.Worksheets
If sht.Name <> "Output" Then
sht.Activate
lrow = sht.Range("B" & Rows.Count).End(xlUp).Row
dest_lrow = Worksheets("Output ").Range("B" &
Rows.Count).End(xlUp).Row
Range("B1").AutoFilter Field:=1, Criteria1:="<>"
Range("A1:d" & lrow).SpecialCells(xlCellTypeVisible).Copy
Worksheets("Output ").Range ("A" & dest_lrow + 1)
Selection.AutoFilter
End If
Next sht
Worksheets("Output ").Select
End Sub

 

[Doug Robbins - Word MVP]

Better to post questions in connection with programming Excel to the
microsoft.public.excel.programming newsgroup rather than to this one which
is for Word VBA.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Joel]

I'm an excel expert with little requests on a Sunday morning. Glad to find
this request. I love to get a jump on ther other experts. I got the errors
out of your program. Not surre if it does exactly whatt you need it to do.


Sub abc()
Dim sht As Worksheet
Dim lrow As Long
Dim dest_lrow As Long
For Each sht In ActiveWorkbook.Worksheets
If sht.Name <> "Output" Then
sht.Activate
lrow = sht.Range("B" & Rows.Count).End(xlUp).Row
dest_lrow = Worksheets("Output").Range("B" & _
Rows.Count).End(xlUp).Row
Sheets(sht.Name).Range("B1").AutoFilter Field:=1, Criteria1:="<>"
Range("A1:d" & lrow).SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("Output").Range("A" & CStr(dest_lrow
+ 1))
Selection.AutoFilter
End If
Next sht
Worksheets("Output").Select
End Sub