Unique list from multiple sheets

[joand]

In a workbook I have about a hundred worksheets. I want to extract from each sheet the data in Column C to a Master sheet. I need to extract only the unique items from the generated list. Is this possible?

Any help would be appreciated.

 

[jeffreybrown]

How about this...

Please test on a copy workbook first!

Create a workbook called Master and this worksheet will be excluded.

Sub MergeMyData()
Dim ws As Worksheet
Application.ScreenUpdating = False

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Master" Then
            ws.Range("C1", ws.Range("C" & ws.Rows.Count).End(xlUp)).Copy Sheets("Master").Range("B" & Rows.Count).End(xlUp).Offset(1)
        End If
    Next ws

With Sheets("Master").Range("B1", Range("B" & Rows.Count).End(xlUp))
    .Sort Key1:=Range("B1"), _
        Order1:=xlAscending, _
        Header:=xlNo, _
        OrderCustom:=1, _
        MatchCase:=False, _
        Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End With
Sheets("Master").Range("B1", Range("B" & Rows.Count).End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Master").Range("A1"), Unique:=True
Sheets("Master").Range("B1", Range("B" & Rows.Count).End(xlUp)).ClearContents

Application.ScreenUpdating = True
End Sub

 

[joand]

thanx jeff. this is great! would this work even if column C has blank cells?

 

[jeffreybrown]

Yes

 

[joand]

great! thanks again

 

[jeffreybrown]

You're welcome