Advanced Filters Macro

[juajar]

Hello, thanks for any help from you guys....

I have a Workbook with 4 sheets. On each sheet on Column A is a list of diferent codes which goes on and on "about 5000 rows". (The number of rows on each sheet vary every day so it would have to selet only the rows which have data on it... aprox. 5000)

The workbook looks like this: (Note that the 4 sheets are not identical, below is for illustartive purposes, each set of data is in column A on its respective sheet)

Libro1
	A	B	C	D	E	F	G
1	ColumnASHEET1		ColumnASHEET2		ColumnASHEET3		ColumnASHEET4
2	Nemo		Nemo		Nemo		Nemo
3	BDVT07150510		BBVT10090912		IFCT05210307		BBTA1999AV
4	BDVT10230915		BBVT10200115		BBVT09290613		BDCU1999CV
5	BDIT04230909		BBVT10200115		BBVT09290613		BEAA1991AV
6	BDVT07150510		BBVT10200115		BBVT09290613		BEAA30229V
7	BDIT04230909		BBVT12200514		BBVT09290613		BEAA30229V
8	BDIT04230909		BBVT12200514		BBVT09290613		BEAA30229V
9	BDIT04230909		BBVT12200514		BBVT09290613		BEAA30229V
10	BDIT04230909		BBVT12200514		BBVT09290613		BEAA4039V
11	BDIT04230909		BCMT07290110		BBVT09290613		BEAA4039V
12	BDVT07150510		BCMT07290110		BBVT09290613		BEAA4039V
13	BDVT07150510		BCMT07290110		BBVT10090912		BEAA4039V
14	BDVT07150510		BCMT07290110		BBVT10090912		BEAA503D2
15	BDVT07150510		IFCT05210307		BBVT12200514		BEAA503D2
16	BDVT07150510		BBVT09290613		BBVT12200514		BEDA059A2V
17	BDVT07150510		...OnandOn		BBVT12200514		BEPB102B9V
18	...OnandOn				...OnandOn		...OnandOn
Hoja5

As you can see on the examples above some of the data is duplicated on the sheets.

What I want to do is to have a sheet 5 which will UNITE all the UNIQUE values on sheets 1, 2, 3 and 4 and put them in sequential order in column A on sheet5. I know I can do it manually but I was looking to automate it with a macro that I could run whenever I want.
THANKS!!!

 

[Barry Katcher]

I'm sure there's a much more elegant way of doing this, but try the code below. It assumes you already have headers in row 1 of all 5 sheets. Incidentally, everything below was simply recorded using the Macro Recorder:

Sub Macro3()

    Sheets("Sheet1").Select
    Range("A2:A10000").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Selection.Copy
    Sheets("Sheet5").Select
    Range("A2").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Application.CutCopyMode = False
    ActiveSheet.ShowAllData
    
    Sheets("Sheet2").Select
    Range("A2:A10000").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Selection.Copy
    Sheets("Sheet5").Select
    Range("A2").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    Application.CutCopyMode = False
    ActiveSheet.ShowAllData
    
    Sheets("Sheet3").Select
    Range("A2:A10000").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Selection.Copy
    Sheets("Sheet5").Select
    Range("A2").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    Sheets("Sheet3").Select
    Application.CutCopyMode = False
    ActiveSheet.ShowAllData
    
    Sheets("Sheet4").Select
    Range("A2:A10000").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Selection.Copy
    Sheets("Sheet5").Select
    Range("A2").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1:A65000").Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("A1").Select
End Sub