在Excel中通过宏添加列到列表时设置列标题 [英] Setting Column Headers When Adding Columns To a Table Via Macro in Excel

问题描述

所以我正在处理这个宏，根据表中的其他列自动向列添加列。所以这里的功能如下：

我在表中列出了许多列CY 2010 - CY 2020。这几年必将改变。然后我想为每个CY列添加一个列给表。此外，我希望这些列的标题与年份匹配，但是现在说Content Volume YEAR

，我有以下代码适当地添加列：

  Sub AddContentValueColumns（）
'
'AddContentValueColumn Macro 
'添加年度车辆内容值列到推进数据表。 
'
 
'
 last = [A1] .Value'单元格包含表格中的CY列数
 
'Debug.Print（last ）
 
对于Count = 1要持续
 Dim oSh As Worksheet 
设置oSh = ActiveSheet 
 oSh.ListObjects（PropTable）。ListColumns.Add 
下一个计数
 
 End Sub 
  

这可以添加列，但是它现在只是将列添加到标记为CY 2021的表的末尾等。

那么如何修改上面的代码来命名列？ / p>

谢谢。

-Sean

解决方案

以下是如何更改列名的示例：

  last = [A1] .Value '单元格包含表格
中的CY列数Count = 1最后
 Dim oSh As Worksheet 
设置oSh = ActiveSheet 
 Dim oLc As ListColumn 
 Set oLc = oSh.ListObjec ts（PropTable）。ListColumns.Add 
 oLc.Name =COLUMN NAME&最后
下一个计数
  

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

解决方案

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

这篇关于在Excel中通过宏添加列到列表时设置列标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！