List uniques from multiple worksheets

[Shazam]

Hi everyone,


I'm trying to do a advance Filter to look through multiple worksheets and copy the uniques into a summary sheet. I have this so far.

[VBA]Sub sortandmove()
Dim Rng As Range
Dim i As Variant
Dim wSht As Worksheet
Application.ScreenUpdating = False
Sheet5.Range("A2:A5000").ClearContents
For Each wSht In ThisWorkbook.Worksheets
If wSht.Name <> "Final List" Then
'wSht.Activate
wSht.Range("A1:B2000").AutoFilter field:=1, Criteria1:="<>"
wSht.Range("A2:B2000").SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Final List").Range("A65536").End(xlUp).Offset(1, 0)
wSht.AutoFilterMode = False
End If
Next wSht
Application.ScreenUpdating = True
End Sub[/VBA]

I tried to add this [VBA], Unique:=True[/VBA] in line below but it does not work.


[VBA]wSht.Range("A2:B2000").SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Final List").Range("A65536").End(xlUp).Offset(1, 0), Unique:=True
[/VBA]

[Norie]

I'm a bit confused.

You say you are using Advanced Filter, but all I can see in the code is AutoFilter.

I'm also not aware of any Unique argument for copying.

[Shazam]

I'm a bit confused.

You say you are using Advanced Filter, but all I can see in the code is AutoFilter.

I'm also not aware of any Unique argument for copying.

Sorry norie your right. Here is a macro record I did. But can it be modified to look through multiple worksheets and just copy the uniques and paste them in the "Final List" worksheet tab in column B?

[vba]
Columns("A:A").Select
Range("A1:A7").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns( _
"B:B"), Unique:=True
[/vba]

[Bob Phillips]

Why not just copy all of the data, sheet by sheet, to the master sheet, and then filter that sheet for uniques?

[Shazam]

Why not just copy all of the data, sheet by sheet, to the master sheet, and then filter that sheet for uniques?

The thing is the user enters the data daily in one of those worksheets ( 12 in Total ). So when the user finish there daily entry all the user has to do is run the macro and it will look through each worksheet and copy just the uniques and paste it into the "Final List" worksheet tab. Do think thats possible?

[Bob Phillips]

Of course it is, but what I was thinking that an item on one sheet might be duplicated on an other, so if you filter by sheet you won't catch that.

Even if you add a sheet daily, you could still do it as I suggested. Repetition isn't going to hurt.

[mdmackillop]

Hi Shazam,
I'd basically follow XLD's suggestion. However, if you master sheet might contain duplicates, you could copy all to a new worksheet, filter and then cut and paste to the Master.

[Shazam]

Hi Shazam,
I'd basically follow XLD's suggestion. However, if you master sheet might contain duplicates, you could copy all to a new worksheet, filter and then cut and paste to the Master.

Thanks mdmackillop xld, & Norie

I'll do your suggestions. Right now I'm using a formula to look through the worksheets and returning unique values But the file keeps on growing and calculation is very slow.


Thanks for the help everyone!