Automatic Row in Table

Question

I'm creating a simple (i think) table and just wanted to be able to input the data on one sheet, click a command button and automatically add a new row to a table with that data.

Anyway I found a good video, followed it to a tee but got a run-time error 9 "subscript out of range"

Not sure the solution, I've tried a couple of things like making sure that my button/table/sheet name is correct.

Here's the code;

Private Sub AddNew_Click()
    Dim myRow As ListRow
    Dim intRows As Integer
    
    intRows = ActiveWorkbook.Worksheets("LoanInfo").ListObjects("LoanTable").ListRows.Count
    Set myRow = ActiveWorkbook.Worksheets("LoanInfo").ListObjects("LoanTable").ListRows.Add(intRows)
    
    myRow.Range(1) = Range("B4")
    myRow.Range(2) = Range("B5")
    myRow.Range(3) = Range("B6")
    myRow.Range(4) = Range("B7")
    myRow.Range(5) = Range("B8")
    myRow.Range(6) = Range("B9")
    myRow.Range(7) = Range("B10")
    
End Sub

I realize that I didn't clarify that yes, my input data starts on column B row 4 of sheet 2 — Owen Watkins
– Owen Watkins, Commented Sep 10 at 17:12
"subscript out of range" could be because in the active workbook a sheet with name "LoanInfo" doesn't exist or doesn't have a table named "LoanTable". What is sheet name of input data ? Is the macro code in the same workbook as the table/input data ? — CDP1802
– CDP1802, Commented Sep 10 at 17:53
It's in the same workbook, the table is named LoanTable (table design) and the sheet name is LoanInfo (caps accurate) — Owen Watkins
– Owen Watkins, Commented Sep 10 at 19:11
Check there are no leading or trailing spaces on the sheet name. Is "sheet 2" the same as Workheets("LoanInfo") ? — CDP1802
– CDP1802, Commented Sep 10 at 19:18

CDP1802 · Accepted Answer · 2025-09-10 17:49:37Z

0

Try

Option Explicit

Private Sub AddNew_Click()

    Dim tb As ListObject, r As ListRow, i As Long
    
    With ActiveWorkbook
        Set tb = .Sheets("LoanInfo").ListObjects("LoanTable")
        Set r = tb.ListRows.Add
        With .Sheets("InputData")
            For i = 1 To 7
                r.Range(i) = .Range("B" & i + 3)
            Next
        End With
    End With
    
End Sub

answered Sep 10 at 17:49

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

VBasic2008 · Accepted Answer · 2025-09-10 20:32:04Z

Copy/Transpose Values from Column to Row

Short

Private Sub AddNew_Click()
' In the workbook containing this code, copy/transposes the 7 values
' from the single-column range 'B4:B10' in sheet 'Sheet2'
' to the corresponding 7 left-most columns of a newly added row
' in table 'LoanTable' of sheet 'LoanInfo'.
' If the table has only 7 columns, you can remove '.Resize(, .Rows.Count)'.
    With ThisWorkbook.Sheets("Sheet2").Range("B4:B10")
        .Worksheet.Parent.Sheets("LoanInfo").ListObjects("LoanTable") _
            .ListRows.Add.Range.Resize(, .Rows.Count).Value = _
            Application.Transpose(.Value)
    End With
End Sub

Variable Infested

Private Sub AddNew_Click2()
' In the workbook containing this code, copy/transposes the 7 values
' from the single-column range 'B4:B10' in sheet 'Sheet2'
' to the corresponding 7 left-most columns of a newly added row
' in table 'LoanTable' of sheet 'LoanInfo'.
' If the table has only 7 columns, you can remove '.Resize(, srg.Rows.Count)'.
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim sws As Worksheet: Set sws = wb.Sheets("Sheet2")
    Dim srg As Range: Set srg = sws.Range("B4:B10")
    
    Dim tws As Worksheet: Set tws = wb.Sheets("LoanInfo")
    Dim tlo As ListObject: Set tlo = tws.ListObjects("LoanTable")
    Dim tlr As ListRow: Set tlr = tlo.ListRows.Add
    Dim trg As Range: Set trg = tlr.Range.Resize(, srg.Rows.Count)
    
    trg.Value = Application.Transpose(srg.Value)

End Sub

Collectives™ on Stack Overflow

Automatic Row in Table

2 Answers 2

Comments

Copy/Transpose Values from Column to Row

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Copy/Transpose Values from Column to Row

Comments

Your Answer

Sign up or log in

Post as a guest

Related