VBA Sort Multiple RSS Feeds

[fluffynicesheep]

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!!)

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

 

[John_w]

Try this macro:

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

 

[John_w]

This checks that a table is present on each sheet and there is a header called Date, and then sorts it:

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

 

[Yongle]

another one for you to try

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]

Could be

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

 

[Yongle]

@DanteAmor
- Excellent - much more succinct than my code!

 

[DanteAmor]

@DanteAmor
- Excellent - much more succinct than my code!

@Yongle
The compliment is appreciated

 

[fluffynicesheep]

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:

ActiveWorkbook.RefreshAll
DoEvents

Before the code from DanteAmor?

 

[DanteAmor]

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:

ActiveWorkbook.RefreshAll
DoEvents

Before the code from DanteAmor?

It is right

 

[fluffynicesheep]

It works perfectly ... thanks so much!