Setting Column Headers When Adding Columns To a Table Via Macro in Excel

Question

So I'm working on this macro that automatically adds columns to a table based on other columns in the table. So here's the functionality:

I have a number of columns in this table headed "CY 2010" - "CY 2020". These years will inevitably change. I then want to add a column to the table for each "CY" column. Furthermore, I'd like the headers of these columns to match the year, but say "Content Volume YEAR"

currently, I have the following code that adds columns appropriately:

Sub AddContentValueColumns()
'
' AddContentValueColumn Macro
' Adds yearly vehicle content value columns to Propulsion data table.
'

'
    last = [A1].Value               ' cell contains number of "CY" columns in sheet

'   Debug.Print (last)

    For Count = 1 To last
        Dim oSh As Worksheet
        Set oSh = ActiveSheet
        oSh.ListObjects("PropTable").ListColumns.Add
    Next Count

End Sub

This works to add the columns, but it currently just adds the columns to the end of the table labeled as "CY 2021", etc.

So how do I modify that code above to name the columns?

Thanks.

-Sean

mr.Reband mr.Reband · Accepted Answer · 2013-06-27T12:41:53

Here's an example of how to change the column name:

last = [A1].Value               ' cell contains number of "CY" columns in sheet
For Count = 1 To last
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    Dim oLc As ListColumn
    Set oLc = oSh.ListObjects("PropTable").ListColumns.Add
    oLc.Name = "COLUMN NAME" & last
Next Count

Setting Column Headers When Adding Columns To a Table Via Macro in Excel

1 Answers