Add multiple columns to table

bleds

New Member
Joined
May 16, 2019
Messages
1
Hello, I've searched the forum and thoroughly as I can and haven't found a solution to my issue. Apologies if I missed something.

I'm building a project resourcing sheet for my company. I would like to be able for users to enter in the number of weeks in the project into a cell and have t add a corresponding number of columns, i.e. B3=5, add 5 columns to the resourcing table.

The simple version would be generic column header names and simply have excel increment the header titles from Week 0...Week 5. The more flash version would be to title the header with the Monday of each week.

Adding a single column is pretty straightforward:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]ActiveSheet.ListObjects("myTable").ListColumns.Add

And I did see another post with code to add multiple worksheet columns that works but it's not for adding table columns:
[/FONT]
VBA - Inserting a variable number of columns based on the value of a cell
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
See if there's something in here that helps:

Code:
Private Sub CommandButton1_Click()

Dim tbl As ListObject
Dim WeekCount As Integer
Dim DeleteColCount As Integer
Dim rngDelete As Range
Dim d As Integer
Dim i As Integer
Const LastColumnB4Weeks As Integer = 4

Set tbl = ListObjects("Table1")
With tbl

    WeekCount = Range("NumWeeks")
    
    DeleteColCount = .ListColumns.Count - LastColumnB4Weeks
    
    If DeleteColCount > 0 Then
        Set rngDelete = .DataBodyRange.Offset(, LastColumnB4Weeks).Resize(, DeleteColCount)
        rngDelete.ClearContents
        For d = DeleteColCount To 1 Step -1
            .ListColumns(LastColumnB4Weeks + d).Delete
        Next d
    End If
    
    For i = 1 To WeekCount
        .ListColumns.Add (LastColumnB4Weeks + i)
        .ListColumns(LastColumnB4Weeks + i).Name = "Week" & i - 1
    Next i
End With

End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

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