Make the new row inside the table

Question

Sub add_product()
' add_product Macro
' ADD PRODUCT MACRO
' TeachExcel.com
' Takes data from one worksheet and stores in in the next empty row on another worksheet.

Dim sourceSheet As Worksheet
Dim dataSheet As Worksheet
Dim nextRow As Long

' Make some sheet variables so we can use those instead of hard-coding sheet references in the code.
Set sourceSheet = Sheets("add product")
Set dataSheet = Sheets("products")

' Get the next empty row from the Data sheet.
nextRow = dataSheet.Range("A" & dataSheet.Rows.Count).End(xlUp).Offset(1).Row

' Input the form values into the Data sheet.
dataSheet.Cells(nextRow, 1).Value = sourceSheet.Range("J5").Value
dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("J7").Value
dataSheet.Cells(nextRow, 3).Value = sourceSheet.Range("J9").Value
dataSheet.Cells(nextRow, 4).Value = sourceSheet.Range("J11").Value
dataSheet.Cells(nextRow, 5).Value = sourceSheet.Range("J13").Value

'Clear Data
sourceSheet.Range("J7").Value = ""
sourceSheet.Range("J9").Value = ""
sourceSheet.Range("J11").Value = ""
sourceSheet.Range("J13").Value = ""
End Sub

I want to add the new Row inside the table enter image description here But as you see in the photo next, the data going outsite the table enter image description here

It seems good this code for you to add a new product at the end of your table. What do you mean "inside the table" ? — jacouh, Mar 05 '23 at 21:58
check the [screen shots](https://i.stack.imgur.com/jHu3N.png) — Yassine Hendaoui, Mar 05 '23 at 22:26
@jacouh open the [screenshot](https://i.stack.imgur.com/jHu3N.png), the table ends on F7 after I added the last row on F8 using [form](https://i.stack.imgur.com/gGRJ2.png) — Yassine Hendaoui, Mar 05 '23 at 22:58
`.End(xlUp).Row` will give the last row of the table even if there are blank rows in the table so offset(1) will be outside the table. What it the table name ? — CDP1802, Mar 05 '23 at 23:01
https://stackoverflow.com/questions/29061072/print-multiple-excel-sheets-in-a-specific-order — Tim Williams, Mar 05 '23 at 23:35

CDP1802 · Answer 1 · 2023-03-06T21:34:38.013

0

Option Explicit
Sub add_product()

    Dim wsSource As Worksheet, wsData  As Worksheet
    Dim n As Long, i As Long, r As Long
    
    Set wsSource = Sheets("add product")
    Set wsData = Sheets("products")
    
    With wsData.ListObjects("table1")
        .ListRows.Add
        n = .ListRows.Count
                
        ' Input the form values into the Data sheet.
        For i = 1 To 5
            r = 3 + i * 2
            .DataBodyRange.Cells(n, i) = wsSource.Cells(r, "J")
            If r >= 7 Then
               wsSource.Cells(r, "J") = ""
            End If
        Next
    End With
End Sub

edited Mar 06 '23 at 21:34

answered Mar 05 '23 at 23:38

CDP1802

13,871
2
7
17

**@CDP1802** Compile error ,DataBodyRange.Cells(n, i) = wsSource.Cells(r, "J") – Yassine Hendaoui Mar 06 '23 at 21:20
@YassineHendaoui replace comma with dot at beginning – CDP1802 Mar 06 '23 at 21:35

score 0 · Answer 2 · answered Mar 05 '23 at 23:40

0

You can resize the table like so

dataSheet.ListObjects("Table1").Resize dataSheet.Range("$A$1:$F$" & nextRow)

answered Mar 05 '23 at 23:40

Kairu

381
1
9

VBasic2008 · Answer 3 · 2023-03-06T04:14:19.343

Write To a New Row of an Excel Table (ListObject)

Instead of
```
.Range("A1").ListObject
```
- ... if the table is the first or the only table, you will use (better):
```
.ListObjects(1)
```
- ... if you know the name of the table, e.g. Table1, you will use (best):
```
.ListObjects("Table1")
```

The Code

Sub AddProduct()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim sws As Worksheet: Set sws = wb.Sheets("Add Product")
    Dim srg As Range: Set srg = sws.Range("J5,J7,J9,J11,J13")
    
    Dim sCell As Range
    
    For Each sCell In srg.Cells
        If Len(CStr(sCell.Value)) = 0 Then
            MsgBox "No blank fields allowed.", vbCritical, "Add Product"
            Exit Sub
        End If
    Next sCell
    
    Dim dws As Worksheet: Set dws = wb.Sheets("Products")
    Dim drg As Range: Set drg = dws.Range("A1").ListObject.ListRows.Add.Range
    
    Dim dc As Long
    
    For Each sCell In srg.Cells
        dc = dc + 1
        drg.Cells(dc).Value = sCell.Value
        If dc > 1 Then sCell.ClearContents
    Next sCell

    MsgBox "Product added.", vbInformation, "Add Product"
    
End Sub

Solved! I just changed nextRow = dataSheet.Range("A" & dataSheet. **Rows** .Count).End(xlUp).Offset(1).Row **to** nextRow = dataSheet.Range("A" & dataSheet. **ListObjects** .Count).End(xlUp).Offset(1).Row **Thank you all for trying to help me <3** — Yassine Hendaoui, Mar 06 '23 at 21:38

Make the new row inside the table

3 Answers3

Write To a New Row of an Excel Table (ListObject)