Automatically adding columns to RSS tables in excel

fluffynicesheep

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

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,604
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
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,604
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
 

fluffynicesheep

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

veyselemre

Board Regular
Joined
Mar 16, 2006
Messages
76
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
 

veyselemre

Board Regular
Joined
Mar 16, 2006
Messages
76
Office Version
  1. 2010
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,122,230
Messages
5,594,947
Members
413,953
Latest member
Arthur1471

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