Automatically adding columns to RSS tables in excel

fluffynicesheep

Board Regular
Joined
Oct 27, 2009
Messages
69
Hi,

I have a spreadsheet with numerous tabs in. Every single tab has a similar RSS feed imported into it (so it becomes a table). At the moment the final column on each tab (and therefore in each table) is always going to be column H.

Is there any easy way to therefore automate the following through VBA:

1) Add 1 blank column to the end of every table in every tab (Column I), and
2) format column I as a UK date, and then
3) Enter the datevalue formula into column I - so it converts column H into a date (e.g. =DATEVALUE([@pubDate]))
4) Then change the heading of the new column I to "Date"

I know it's a bit of a long shot, but thought it best to ask before I set off making the changes manually myself!

Thanks
 
Last edited:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Try this macro. Note that although NumberFormat looks like an American date, it displays the date in the default format for your locale. Or you can use your own date format string.

Code:
Public Sub Add_Column_To_Table_In_Each_Sheet()

    Dim ws As Worksheet
    Dim table As ListObject
    
    For Each ws In ActiveWorkbook.Worksheets
        Set table = ws.ListObjects(1)
        table.ListColumns.Add
        With table.ListColumns(table.ListColumns.Count)
            .Range(1, 1).Value = "Date"
            .DataBodyRange.NumberFormat = "m/d/yyyy"
            .DataBodyRange.Formula = "=DATEVALUE([@pubDate])"
        End With
    Next
    
End Sub
 
Upvote 0
Maybe not all sheets have a table then.
Code:
Public Sub Add_Column_To_Table_In_Each_Sheet()

    Dim ws As Worksheet
    Dim table As ListObject
    
    For Each ws In ActiveWorkbook.Worksheets
        Set table = Nothing
        On Error Resume Next
        Set table = ws.ListObjects(1)
        On Error GoTo 0
        If Not table Is Nothing Then
            table.ListColumns.Add
            With table.ListColumns(table.ListColumns.Count)
                .Range(1, 1).Value = "Date"
                .DataBodyRange.NumberFormat = "m/d/yyyy"
                .DataBodyRange.Formula = "=DATEVALUE([@pubDate])"
            End With
        End If
    Next
    
End Sub
 
Upvote 0
Hi,

Sorry to reopen this ..but I've just added more tables/worksheets into the existing workbook, and wanted to know if there was any way that I could use the above code again, but with the new tables only? So this would mean that the table would have to end at column H, in order for the macro to then kick in ....

If the table already ended after column H, it means that the last code had been run previously, and I therefore don't need to add another column.

If anyone can help again that would be fantastic!
 
Upvote 0
Code:
Public Sub Add_Column_To_Table_In_Each_Sheet()

    Dim ws As Worksheet
    Dim table As ListObject


    For Each ws In ActiveWorkbook.Worksheets
        Set table = Nothing
        On Error Resume Next
        Set table = ws.ListObjects(1)
        On Error GoTo 0
        If Not table Is Nothing Then
            If table.ListColumns.Count < 8 Then
                table.ListColumns.Add
                With table.ListColumns(table.ListColumns.Count)
                    .Range(1, 1).Value = "Date"
                    .DataBodyRange.NumberFormat = "m/d/yyyy"
                    .DataBodyRange.Formula = "=DATEVALUE([@pubDate])"
                End With
            End If
        End If
    Next


End Sub
 
Upvote 0
Code:
Public Sub Add_Column_To_Table_In_Each_Sheet()
    Dim ws As Worksheet
    Dim table As ListObject


    For Each ws In ActiveWorkbook.Worksheets
        If ws.ListObjects.Count > 0 Then
            Set table = ws.ListObjects(1)
            If table.ListColumns.Count < 8 Then
                With table.ListColumns.Add
                    .Range(1, 1).Value = "Date"
                    .DataBodyRange.NumberFormat = "m/d/yyyy"
                    .DataBodyRange.Formula = "=DATEVALUE([@pubDate])"
                End With
            End If
        End If
    Next


End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

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
Back
Top