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 · Accepted Answer · 2013-06-27 12:41:53Z

5

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

answered Jun 27, 2013 at 12:41

mr.Reband

2,4312 gold badges20 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Doug Glancy Over a year ago

+1, I never realized ListColumns had names! This will simplify some code.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related