I am attempting a supply chain builder.
I want the user to press a button to activate a macro that will prompt to identify how many points are in the supply chain.
The number input would then create a table three columns wide by X number of supply points entered.
I need the tables to start on N4 not A1.
From there, each time they press the button to generate a new table, that table would need to appear below the one created prior.
Some users may have three chains, others may have ten. In my template a user would press the macro button for each Supply Chain. Each time they do this it should create a table underneath the prior created table, with a blank row between adjacent tables for separation.
I did attempt a macro that would modify an existing table. My issue with that is when you resize a table it won't work if it ends up overflowing into one below.
I want it to be dynamic like I wrote above.
Currently using this code to get the first table to paste.
Sub DynamicRange()
'Best used when first column has value on last row and first row has a value in the last column
Dim i As Variant
i = InputBox("How many DFSP's in this Supply Chain?", "Enter Quantity")
Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range
Set sht = Worksheets("test")
Set StartCell = Range("c" & i)
'Find Last Row and Column
LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column
'Select Range
sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select
Dim objTable As ListObject
Set objTable = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes)
End Sub
How to avoid using Select in Excel VBA
Microsoft documentation:
Sub DynamicRange()
Const COL_CNT = 3 ' cols count in the table (ListObject)
Const COL_KEY = 1 ' used to determine the last row
Dim i As Variant
i = InputBox("How many DFSP's in this Supply Chain?", "Enter Quantity")
If Not IsNumeric(i) Then
MsgBox "Please input a number.", vbCritical
Exit Sub
End If
If i < 1 Then Exit Sub
Dim sht As Worksheet
Dim LastRow As Long
Set sht = Worksheets("test")
LastRow = sht.Cells(sht.Rows.Count, COL_KEY).End(xlUp).Row
With sht.Cells(LastRow, COL_KEY)
If Len(.Value) > 0 Or (Not .ListObject Is Nothing) Then
LastRow = LastRow + 1
End If
End With
Dim tabRng As Range
Set tabRng = sht.Range("A" & LastRow).Resize(i + 1, COL_CNT)
Dim objTable As ListObject
Set objTable = ActiveSheet.ListObjects.Add(xlSrcRange, tabRng, , xlYes)
objTable.HeaderRowRange.Value = Array("Col1", "Col2", "Col3") ' for testing
End Sub
Update:
Question: I need the tables to start on N4 when the user starts to use this macro
Sub DynamicRange()
Const COL_CNT = 3 ' cols count of the table (ListObject)
Const COL_KEY = "N" ' used to determine the last row
Const FIRST_ROW = 4
Dim i As Variant
i = InputBox("How many DFSP's in this Supply Chain?", "Enter Quantity")
If Not IsNumeric(i) Then
MsgBox "Please input a number.", vbCritical
Exit Sub
End If
If i < 1 Then Exit Sub
Dim sht As Worksheet
Dim LastRow As Long
Set sht = Worksheets("test")
LastRow = sht.Cells(sht.Rows.Count, COL_KEY).End(xlUp).Row
With sht.Cells(LastRow, COL_KEY)
If Len(.Value) > 0 Or (Not .ListObject Is Nothing) Then
LastRow = LastRow + 1
End If
If LastRow < FIRST_ROW Then LastRow = 4
End With
Dim tabRng As Range
Set tabRng = sht.Cells(LastRow, COL_KEY).Resize(i + 1, COL_CNT)
Dim objTable As ListObject
Set objTable = ActiveSheet.ListObjects.Add(xlSrcRange, tabRng, , xlYes)
objTable.HeaderRowRange.Value = Array("Col1", "Col2", "Col3") ' for testing
End Sub