VBA Sort Multiple RSS Feeds

fluffynicesheep

Board Regular
Joined
Oct 27, 2009
Messages
67
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,746
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,746
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,977
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,619
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
67
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,619
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,129,304
Messages
5,635,426
Members
416,857
Latest member
m2pk

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