Sub RetrieveList()
Dim wbSource As Workbook
Dim wsTarget As Worksheet
Dim sTargetSheet As String
Dim sFileName As String
Dim sTable As String
Dim lstTable As ListObject
Application.ScreenUpdating = False
'Set name of data table and worksheet that holds it here
sTable = "tblCurrent"
Set wsTarget = ActiveWorkbook.Worksheets("Data")
Set lstTable = wsTarget.ListObjects(sTable)
'Set name of the worksheet that holds the data in the remote file
sTargetSheet = "Sheet1"
'Clear the data in the table
If lstTable.ListRows.Count > 0 Then lstTable.DataBodyRange.Delete
'Open remote file
sFileName = CStr(Application.GetOpenFilename)
If sFileName = "Cancel" Then Exit Sub
Set wbSource = Workbooks.Open(sFileName)
'Copy data from remote to local file
With wbSource.Worksheets(sTargetSheet)
.Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row).Copy
End With
lstTable.Range.Cells(1, 1).PasteSpecial xlPasteValues
'Close the remote workbook
wbSource.Close savechanges:=False
'Sort the table to group blank rows
With lstTable
With .Sort
.SortFields.Clear
.SortFields.Add Key:=Range(sTable & "[[#All],[Current Month Amount]]"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
End With
'Delete blank rows
lstTable.Range.AutoFilter Field:=1, Criteria1:="="
With wsTarget
.Rows("2:" & .Range("A" & .Rows.Count).End(xlUp).Row).Delete
End With
lstTable.Range.AutoFilter Field:=1
End Sub