Advanced Filter Paste into Excel Table

[rilzniak]

Hey everyone,

I've been struggling with a problem and can't seem to come up with a solution so I'm hoping someone here knows a quick fix.

My goal is to use the advanced filter to remove duplicates from column H - MRColLetter - and paste it into the MRTable I've got on another worksheet. The problem is that when I paste the data it not only pastes the header row from the advanced filter in the wrong position - second row of the table - but what I've got also doesn't re-size the table to accommodate the new entries. Below is the code I'm using. Any suggestions?

Option Explicit

Sub CreateMasterRegionList()

'MR = MasterRegion --> refers to the column on the BranchMaster worksheet
'BM = BranchMaster --> refers to the name of the worksheet

Dim MRColNo As Integer, LastBMRow As Integer
Dim MRColLetter As String, BM As String

BM = "BranchMaster"
' Find what column the MasterRegion head is in
MRColNo = Application.Match("MasterRegion", Worksheets(BM).Rows(1), 0)
'Turn the MasterRegion column number into a letter
MRColLetter = Split(Cells(1, MRColNo).Address, "$")(1)
'Count the number of rows on the BranchMaster worksheet
LastBMRow = Worksheets(BM).Cells(1, MRColNo).End(xlDown).Row

    'Deletes the entries which currently exist in the MRTable and copies the entries in the MR column on the BM worksheet and pastes them in the MRTable without duplicates
    With Range("MRTable").ListObject
        If Not .DataBodyRange Is Nothing Then
            .DataBodyRange.Delete
            .ListRows.Add alwaysinsert:=True
        End If
        Sheets(BM).Columns(MRColLetter).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("MRTable").ListObject, Unique:=True
    End With

End Sub

Thanks in advance,

rilz

 

[DanteAmor]

Try with this:

Sub CreateMasterRegionList()


    'MR = MasterRegion --> refers to the column on the BranchMaster worksheet
    'BM = BranchMaster --> refers to the name of the worksheet
    
    Dim MRColNo As Integer, LastBMRow As Integer
    Dim MRColLetter As String, BM As String
    
    BM = "BranchMaster"
    ' Find what column the MasterRegion head is in
    MRColNo = Application.Match("MasterRegion", Worksheets(BM).Rows(1), 0)
    'Turn the MasterRegion column number into a letter
    MRColLetter = Split(Cells(1, MRColNo).Address, "$")(1)
    'Count the number of rows on the BranchMaster worksheet
    LastBMRow = Worksheets(BM).Cells(1, MRColNo).End(xlDown).Row


    'Deletes the entries which currently exist in the MRTable and copies the entries in the MR column on the BM worksheet and pastes them in the MRTable without duplicates
    With Range("MRTable").ListObject
        
        If Not .DataBodyRange Is Nothing Then
            .DataBodyRange.Delete
            .ListRows.Add alwaysinsert:=True
        End If
        Sheets(BM).Columns(MRColLetter).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("MRTable").ListObject, Unique:=True


[COLOR=#0000ff]        .Resize .Range.CurrentRegion[/COLOR]
    
    End With


End Sub