Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tripla - 28 Apr 2007 20:21 GMT

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

Reply to this Message

Doug Robbins - Word MVP - 29 Apr 2007 15:05 GMT

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.

Signature

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

>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
[quoted text clipped - 36 lines]
> Worksheets("Output ").Select
> End Sub

Reply to this Message

Joel - 29 Apr 2007 17:28 GMT

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

> 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
[quoted text clipped - 36 lines]
> Worksheets("Output ").Select
> End Sub

Reply to this Message