Button to add column to the end of a table

[gaudrco]

Hi,

I would like to be able to assign a command button to add a new column to the end of my table. Each new column added should appear to the right of the last new column add. The command button will be on the activesheet but the table in which I am trying to add columns to will be on sheet “Competitor Overview Data”. My table range is from D4:S52 and the name of the range is “CompTable”.

Any ideas?

 

[FormR]

Hi, you could try:

Sub addtablecolumn()
With Sheets("Competitor Overview Data").ListObjects("CompTable")
    .ListColumns.Add (.ListColumns.Count + 1)
End With
End Sub

 

[gaudrco]

That did not work. I tried to adjust it too by adding the CommandButton to the sub. This is what I tried:

Private Sub CommandButton2_Click()
With Sheets("Competitor Overview Data").ListObjects("CompTable")
    .ListColumns.Add (.ListColumns.Count + 1)
End With
End Sub

A message popped up saying "subscript out of range"

Also, it doesn't matter if the new column comes off of "CompTable. I just need the columns to be add after column S because that's where the table ends right now. As long as the button adds new columns to the right of the last column starting on column S, I should be good.

 

[MARK858]

Subscript out of range normally means that something is named incorrectly. Double check that your sheet name and table name exactly match including capitalisation and for leading and trailing spaces.

 

[gaudrco]

I typed everything correct and it still doesn’t work. How about a code for the command button to add a column to the right of column S on the “Competitor Overview Data” tab. With every button push putting a new column to the right of the last added column?

 

[MARK858]

FormR's code works fine for me. First of all you describe it as a table then you state "the name of the range is “CompTable”".
So is it an actual table (where you selected the range then clicked Insert then clicked table) or a range?

If it is a table make sure that the Immediate window is open then run the code below. Copy and Paste the results in the thread (do not type the results in the thread, copy and paste it).

Sub ListTables()
    Dim xTable As ListObject, xSheet As Worksheet
 
    For Each xSheet In Worksheets
        For Each xTable In xSheet.ListObjects
            Debug.Print "| &"; xSheet.Name; "& |", Len(xSheet.Name) & " "; "| &"; xTable.Name; "& |", Len(xTable.Name)
        Next xTable
    Next
End Sub

How about a code for the command button to add a column to the right of column S

Not until we sort out what your issue is.

 

[gaudrco]

It is not an actual table, it’s a defined range. And I’m not sure if this makes a difference but the headers of my defined range are not in row 1, they are in row 4.

 

[MARK858]

It is not an actual table

That is why the code by FormR doesn't work for you try

Sub ResizeNamedRange2()
    Dim xName As Name
    Set xName = ActiveWorkbook.Names.Item("CompTable")
    With xName
        .RefersTo = .RefersToRange.Resize(, Sheets("Competitor Overview Data").Range("CompTable").Columns.Count + 1)
    End With
End Sub

 

[FormR]

It is not an actual table

Maybe you could change it to an actual table?

 

[gaudrco]

I changed it to a table an it worked! The one thing is does not do is match the formatting of the entire column. Since my table headers start on row 4, everything above thenew columns is blank formatting. The new column only matches the formatting ofthe table range but I would like it to match everything that is in the column includingthe formatting above and below the table. Can this be adjusted?

Also, just to note, I tried MARK858’s code to achieve thisbut I couldn’t seem to get it right.