Advanced Filters Macro

juajar

New Member
Joined
Aug 25, 2006
Messages
20
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
ABCDEFG
1ColumnASHEET1ColumnASHEET2ColumnASHEET3ColumnASHEET4
2NemoNemoNemoNemo
3BDVT07150510BBVT10090912IFCT05210307BBTA1999AV
4BDVT10230915BBVT10200115BBVT09290613BDCU1999CV
5BDIT04230909BBVT10200115BBVT09290613BEAA1991AV
6BDVT07150510BBVT10200115BBVT09290613BEAA30229V
7BDIT04230909BBVT12200514BBVT09290613BEAA30229V
8BDIT04230909BBVT12200514BBVT09290613BEAA30229V
9BDIT04230909BBVT12200514BBVT09290613BEAA30229V
10BDIT04230909BBVT12200514BBVT09290613BEAA4039V
11BDIT04230909BCMT07290110BBVT09290613BEAA4039V
12BDVT07150510BCMT07290110BBVT09290613BEAA4039V
13BDVT07150510BCMT07290110BBVT10090912BEAA4039V
14BDVT07150510BCMT07290110BBVT10090912BEAA503D2
15BDVT07150510IFCT05210307BBVT12200514BEAA503D2
16BDVT07150510BBVT09290613BBVT12200514BEDA059A2V
17BDVT07150510...OnandOnBBVT12200514BEPB102B9V
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!!!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
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:

Code:
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
 

Forum statistics

Threads
1,136,877
Messages
5,678,290
Members
419,753
Latest member
Sallylwy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top