Automatically adding columns to RSS tables in excel

fluffynicesheep

New Member
Joined
Oct 27, 2009
Messages
49
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:

John_w

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

New Member
Joined
Oct 27, 2009
Messages
49
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
55
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
55
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:

Forum statistics

Threads
1,084,733
Messages
5,379,498
Members
401,607
Latest member
Zemexi

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top