Button to add column to the end of a table

gaudrco

Board Regular
Joined
Aug 16, 2019
Messages
203
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?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Hi, you could try:

Code:
Sub addtablecolumn()
With Sheets("Competitor Overview Data").ListObjects("CompTable")
    .ListColumns.Add (.ListColumns.Count + 1)
End With
End Sub
 
Last edited:
Upvote 0
That did not work. I tried to adjust it too by adding the CommandButton to the sub. This is what I tried:

Code:
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.
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
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?
 
Upvote 0
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).

Code:
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.
 
Upvote 0
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.
 
Upvote 0
It is not an actual table

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


Code:
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
 
Last edited:
Upvote 0
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.

 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top