VBA Sort Multiple RSS Feeds

fluffynicesheep

Board Regular
Joined
Oct 27, 2009
Messages
65
Hi,

I have a workbook with a number of different rss feeds imported into different tabs/sheets. All the feeds are different table lengths, but they all have the name of a header in common. I wonder if there is any easy way therefore of doing it all at once?

At the moment, my code just goes to a named tab and sorts the named table by the [date] column ... I would have to write this hundreds if times, so wondered if there was a way of going through the tabs one by one, seeing if there is a table present and if so, if there is a header called [Date] then it sorts it by descending order?

Below is the code I'm using to sort each one separately at the moment (first 2 tables out of hundreds only!!)

Code:
ActiveWorkbook.Worksheets("Charity").ListObjects("Table307").Sort.SortFields _        .Clear
    ActiveWorkbook.Worksheets("Charity").ListObjects("Table307").Sort.SortFields _
        .Add Key:=Range("Table307[[#All],[Date]]"), SortOn:=xlSortOnValues, Order _
        :=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Charity").ListObjects("Table307").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With




ActiveWorkbook.Worksheets("Finance").ListObjects("Table327").Sort.SortFields _
        .Clear
    ActiveWorkbook.Worksheets("Finance").ListObjects("Table327").Sort.SortFields _
        .Add Key:=Range("Table327[[#All],[Date]]"), SortOn:=xlSortOnValues, Order _
        :=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Finance").ListObjects("Table327").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


Any help would be greatly appreciated!

Thanks
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,424
Try this macro:
Code:
Public Sub Sort_Table_In_Each_Sheet()

    Dim ws As Worksheet
    Dim table As ListObject
    
    For Each ws In ActiveWorkbook.Worksheets
        Set table = ws.ListObjects(1)
        table.Sort.SortFields.Clear
        table.Sort.SortFields.Add2 key:=Range(table.Name & "[Date]"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        With table.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    Next
    
End Sub
I will change the above code to

... seeing if there is a table present and if so, if there is a header called [Date] then it sorts it by descending order
 
Last edited:

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,424
This checks that a table is present on each sheet and there is a header called Date, and then sorts it:
Code:
Public Sub Sort_Table_In_Each_Sheet()

    Dim ws As Worksheet
    Dim table As ListObject, dateHeader As ListColumn
    
    For Each ws In ActiveWorkbook.Worksheets
        Set table = Nothing
        Set dateHeader = Nothing
        On Error Resume Next
        Set table = ws.ListObjects(1)
        Set dateHeader = table.ListColumns("Date")
        On Error GoTo 0
        If Not table Is Nothing And Not dateHeader Is Nothing Then
            table.Sort.SortFields.Clear
            table.Sort.SortFields.Add2 key:=dateHeader.DataBodyRange, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
            With table.Sort
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End If
    Next
    
End Sub
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,746
Office Version
  1. 365
Platform
  1. Windows
another one for you to try


Code:
Sub LoopAllTables()

Dim T As ListObject, Ws As Worksheet, P As String

For Each Ws In ThisWorkbook.Worksheets
    For Each T In Ws.ListObjects
        On Error Resume Next
        P = T.ListColumns("Date").Parent        'used to see if header "Date" is present
        If Err.Number > 0 Then
            On Error GoTo 0
        Else
            T.Sort.SortFields.Clear
            T.Sort.SortFields.Add2 Key:=Range(T.Name & "[[#All],[Date]]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
            With T.Sort
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End If
    Next T
Next Ws

End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Could be

Code:
Sub SortTables()
    Dim ws As Worksheet, t As ListObject
    On Error Resume Next
    For Each ws In Sheets
        For Each t In ws.ListObjects
            t.Range.Sort key1:=t.Name & "[DATE]", Order1:=xlDescending
        Next
    Next
End Sub
 
Last edited:

fluffynicesheep

Board Regular
Joined
Oct 27, 2009
Messages
65
Hi all,

Thanks for this, I'm just going to give it a run through now.

Just to confirm, in order to [refresh all] feeds before the sort takes place should I just insert:

Code:
ActiveWorkbook.RefreshAll
DoEvents

Before the code from DanteAmor?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Hi all,

Thanks for this, I'm just going to give it a run through now.

Just to confirm, in order to [refresh all] feeds before the sort takes place should I just insert:

Code:
ActiveWorkbook.RefreshAll
DoEvents

Before the code from DanteAmor?

It is right
 

Watch MrExcel Video

Forum statistics

Threads
1,109,028
Messages
5,526,335
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top