create master list from varying worksheets

All

Seeking some assistance please...

I have a workbook with 14 worksheets that have member numbers (ie each member has a unique number) in each sheet... I would like to make a master list of member numbers on a new sheet.... as the member numbers may change each month this needs to be 'dynamic' for want of a better word....

I have some buttons and vba etc that do other formatting etc, however, I can't figure this one.... I will always be 'looking' at column A in each worksheet with the first 'number' in row 2....

so ideally the VBA would go to each worksheet, copy members numbers from WS1, column A2, and paste in new worksheet at c2. then go to WS2, column A2, and copy and paste below the numbers copied from WS1....or something similar.... I need to end up with a list of Unique Member umbers so duplicates would be removed....

hope I haven't confused the issue with my explanation....

TIA
Shaz0503

Submit a copy of you workbook. You may be able to do what you need with a formula.

stnkynts

please see dummy file attached...

WS 1;2;4 and 5 have "data"...

Final_Format columns K-O show how I would like the data to display.... no dups but a consolidated list of numbers from WS 1;2;4 and 5

hope this makes sense

S

Sorry for the delay. I believe this does exactly as your spreadsheet example indicates:

Sub Unique_List()
Dim wsFinal As Worksheet:   Set wsFinal = Sheets("Final_Format")
Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In Worksheets
    If ws.Name <> wsFinal.Name Then
        ws.Range("A2:A" & ws.Range("A" & Rows.Count).End(xlUp).Row).Copy Destination:=wsFinal.Range("C" & Rows.Count).End(xlUp).Offset(1, 0)
    End If
Next ws

wsFinal.Range("C1:C" & wsFinal.Range("C" & Rows.Count).End(xlUp).Row).AdvancedFilter xlFilterCopy, , wsFinal.Range("M1"), True

Application.ScreenUpdating = True

End Sub

Thanks stnkynts

I will test this out and let you know...

rgds

s

stnkynts

Thanks for the above - works fine my 'real' file .

Have another issue with this file but will post new question...

Thanks you for your assistance

rgds

shaz0503