Add rows to a table Macro

tupac_developer 

It seems like you're encountering an issue when trying to insert rows into a specific table in Excel using VBA, especially when there are multiple tables on the sheet. The error you're receiving (Subscript out of range) usually happens when an object you're trying to reference doesn't exist or is incorrectly referenced.

To resolve the issue, you need to ensure the following:

1. You're referencing the correct table.
2. You're correctly inserting rows into the table.
3. You're handling potential conflicts with other tables on the sheet.

Let me guide you step by step to help fix the issue.

Correcting the Code

Instead of directly inserting values from one table to another, you need to ensure that:

• You correctly refer to the tables (TaskTable and ActionQuery).
• You loop through and add the required rows.

Example Code for Adding Rows to a Table

Here’s an example that demonstrates how you can add rows to a specific table:

Vba Code is untested, please backup your file beforehand.

Sub AddRowsToTable()
    Dim TaskTable As ListObject
    Dim ActionQuery As ListObject
    Dim Number_of_Rows As Long
    Dim i As Long
    
    ' Make sure you refer to the correct table names here
    ' Assuming "TaskTable" is the name of the table you want to add rows to
    ' and "ActionQuery" is the name of the table from which you are copying data
    
    ' Define your tables explicitly
    Set TaskTable = ThisWorkbook.Sheets("Sheet1").ListObjects("TaskTable")
    Set ActionQuery = ThisWorkbook.Sheets("Sheet1").ListObjects("ActionQuery")
    
    ' Get the number of rows in ActionQuery table (filtered rows are not counted)
    Number_of_Rows = ActionQuery.ListRows.Count
    
    ' Loop through each row in the ActionQuery table and add a new row in the TaskTable
    For i = 1 To Number_of_Rows
        ' Add a new row at the end of the TaskTable
        With TaskTable.ListRows.Add
            ' Copy the data from ActionQuery to the new row in TaskTable
            .Range.Value = ActionQuery.ListRows(i).Range.Value
        End With
    Next i
    
    MsgBox "Rows added successfully!"
    
End Sub

Breakdown of the Code:

1. ListObject References:
• The TaskTable and ActionQuery are both ListObject variables. You should ensure that the correct table names and worksheet names are specified in the Set statements.

Vba Code is untested, please backup your file beforehand.

Set TaskTable = ThisWorkbook.Sheets("Sheet1").ListObjects("TaskTable")
Set ActionQuery = ThisWorkbook.Sheets("Sheet1").ListObjects("ActionQuery")

1. Number of Rows:
• We use ActionQuery.ListRows.Count to get the number of rows in the ActionQuery table.
2. Loop and Add Rows:
• We loop through each row of the ActionQuery table using For i = 1 To Number_of_Rows.
• Each iteration adds a new row at the end of TaskTable, then copies the corresponding row data from ActionQuery into it.
3. Add and Populate Rows:
• The ListRows.Add function inserts a new row in the TaskTable, and we set its value using .Range.Value.

Important Notes:

• Sheet Names and Table Names: Double-check the worksheet name (Sheet1 in the example) and the table names (TaskTable and ActionQuery). You must replace these with the actual names used in your workbook.
• Filtered Rows: If the ActionQuery table is filtered and you want to insert only the visible rows, you will need a different approach to handle filtered ranges.

Handling Filtered Rows (Optional)

If you want to add only the visible rows from the ActionQuery table when it's filtered, use the following inside the loop:

Vba Code is untested, please backup your file beforehand.

For i = 1 To Number_of_Rows
    If Not ActionQuery.ListRows(i).Range.EntireRow.Hidden Then
        With TaskTable.ListRows.Add
            .Range.Value = ActionQuery.ListRows(i).Range.Value
        End With
    End If
Next i

This additional check ensures that only visible rows are copied.

Testing and Debugging:

• Ensure that your tables (TaskTable and ActionQuery) are correctly named in Excel.
• If you still encounter issues, try using Debug.Print or MsgBox statements to inspect the values of Number_of_Rows or any variables in the loop to verify the code is behaving as expected.The text and the codes was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.