Filter unique List Macro

[MikeG]

i would like to change the macro below to do the following

Rather than paste the results to cells(10, 36) in the current worksheet, instead I want them to be pasted to another sheet called "Master Tables" and to Cells(X,Y) withing that sheet. X and Y will be specified with a value in the macro - e.g. x=12 : y=3

Also, I would like to paste only the values, not the formats.

Finally, I would like the list to be sorted from A to Z.



==================================================
Sub Update_Asset_List()

Range("E10:E15536").AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Cells(10, 36), Unique:=True

End Sub
==================================================


I would appreciate any help.

Thanks,

MikeG

 

[VoG]

I think that Advanced Filter will only copy the unique values to the same sheet. You can easily record a macro to extract uniques, cut to another sheet then sort.

 

[MikeG]

I think that Advanced Filter will only copy the unique values to the same sheet. You can easily record a macro to extract uniques, cut to another sheet then sort.

Thanks Peter - good idea.

 

[ISY]

Hallo

It seems possible to do the operation!

by the following Link:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=786

Demo:
http://www.vbaexpress.com/kb/default.php?action=13&kb_id=786

To be entered in 2 data filters:

Option Explicit

Sub Copy_VoG()
Dim rng As Range

    Application.EnableEvents = False
     'Set Range
    Set rng = ActiveSheet.UsedRange
     'Call function Filterandcopy
    FilterAndCopy rng, "A"
    rng.AutoFilter
End Sub

Function FilterAndCopy(rng As Range, Choice As String)
    
    Dim FiltRng As Range
     'Clear Contents to show just new search data
    Worksheets("Sheet2").Cells.ClearContents
     'Set the column to filter (In This Case 1 or A)
     'Change as required
    rng.AutoFilter Field:=1, Criteria1:=Choice
    rng.AutoFilter Field:=3, Criteria1:=11
    On Error Resume Next
    Set FiltRng = rng.SpecialCells(xlCellTypeVisible).EntireRow
    On Error GoTo 0

    'Copy Data across to sheet 2
    FiltRng.Copy Worksheets("Sheet2").Range("A1")
    'Display Data
    Worksheets("Sheet2").Select
    Range("A1").Select
    Set FiltRng = Nothing
End Function

 

[Datsmart]

Pretty sure I have done this in the past.
Put together some code that seems to work on my test workbook.

Sub FilterUniqueToAnotherSheet()
Dim FilterRange As Range, TargetRange As Range
'Assign variables
    x = 12
    y = 3
    Set FilterRange = Sheets("Sheet1").Range("A1").CurrentRegion
    Set TargetRange = Sheets("Master Tables").Cells(x, y)
'Run Advanced Filter
    Range("A:A").AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=TargetRange, _
        Unique:=True
'This line of code takes you to the worksheet where data was filtered to.
    Sheets("Master Tables").Select
'Sort Unique List
    With ActiveWorkbook.Worksheets("Master Tables").Sort
        .SetRange Cells(x, y).CurrentRegion
        .Header = xlYes
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
'Clear memory of variables
    Set FilterRange = Nothing
    Set TargetRange = Nothing
End Sub

(Using Excel 2007)