Macro Help - Newb here... Creating Table

Hey All,
So i'm definitely not smart with this stuff (which is why I'm coming to you all!)

Here's what we're trying to do. We have a single worksheet and need to keep it on one sheet. The top "table" will be edited by the user. They will have the ability to insert rows and the data as necessary. Below this user-defined table we need two tables that separate the data that was collected.

I'll try to explain this as best as possible and have attached the document as well.

The table that people have the ability to edit/add to contains a model number, description of the item, total quantity of item sent, and the total quantity of that item they would like to return. The macro will need to separate the items by the quantity of items staying vs. the items being returned. The return table should just pull values from the "Return Qty" column (Column E). The Items Staying table will need to do a simple subtraction of Column D minus Column E (Qty minus Return Qty).

I have searched many forums and spent too much time on the interwebs and I'm getting frustrated. Was hoping to find a similar code that I could manipulate to my needs, but again, I suck at this. My big hang up is the fact that people will have the ability to ADD rows to the user defined table. I was going to do some simple formula (if/then statements) to just copy data down, but that ability to add tables makes me feel like the macro is needed.

Any of you able to help out??

Hello vipvanilla,

Welcome to the Forum!

This can be done using VBA. I need a little more information before I start coding a solution for you.

Your top table has 54 rows. Your two bottom tables have only 20 each. This means the maximum entries that could recorded to the bottom tables would be forty.

The top table should start with forty rows. As rows are added to the top table, the macro would add a now to each bottom table. Would this work for you?

Hello vipvanilla,

The macro below has been added to the attached workbook. A button has been added to the "Addendum" worksheet. A new worksheet has also been added named "Tables". This holds the two tables that will be copied below the main table.

Here is the code for the macro "AddTables" in Module2.

Sub AddTables()

    Dim Cnt         As Long
    Dim Data        As Variant
    Dim EndRow      As Range
    Dim EquipList   As Range
    Dim n           As Long
    Dim r           As Long
    Dim RowCnt      As Long
    Dim TableRow    As Long
    Dim TableWks    As Worksheet
    Dim Wks         As Worksheet
    
        Set Wks = Worksheets("Addendum")
        
        Set TableWks = Worksheets("Tables")
        
      ' Entire table range.
        Set EquipList = Wks.Range("A10").CurrentRegion
        
          ' Last row entry in the equipment list.
            Set EndRow = EquipList.Cells(EquipList.Rows.Count, "B").End(xlUp)
            
          ' Check data has been entered.
            If EndRow.Row < EquipList.Row Then Exit Sub
            
          ' Delete any previous Equipment Tables.
            r = EquipList.Row + EquipList.Rows.Count - 1
            n = Wks.Cells(Rows.Count, "A").End(xlUp).Row
                If n > r Then
                    Wks.Range(Wks.Rows(r + 1), Wks.Rows(n)).Delete Shift:=xlUp
                End If
            
              ' Prepare the Equipment Tables.
                TableRow = 3
                TableWks.Rows(3).EntireRow.ClearContents
                TableWks.UsedRange.Offset(3, 0).ClearContents
                
                RowCnt = EndRow.Row - EquipList.Row + 1
                
                  ' Fill in the Equipment Tables.
                    For r = 2 To RowCnt
                        If EquipList.Cells(r, 2) <> "" Then
                            Data = EquipList.Range("A" & r, "C" & r).Value
                            ReDim Preserve Data(1 To 1, 1 To 4)
                            
                            With TableWks
                                .Rows(TableRow).FillDown
                                
                                Data(1, 4) = EquipList.Cells(r, 5).Value
                                .Range(.Cells(TableRow, "F"), .Cells(TableRow, "I")).Value = Data
                        
                                Data(1, 4) = EquipList.Cells(r, 4).Value - Data(1, 4)
                                .Range(.Cells(TableRow, "A"), .Cells(TableRow, "D")).Value = Data
                                
                                TableRow = TableRow + 1
                            End With
                        End If
                    Next r
                    
           ' Add Equipment Remaining at Facility Table.
             r = EquipList.Rows.Count + EquipList.Row + 2
             TableWks.Range("A1").CurrentRegion.Copy Wks.Cells(r, "A")
                    
           ' Add Equipment Returned to facility Table.
             r = r + TableWks.Range("F1").CurrentRegion.Rows.Count + 2
             TableWks.Range("F1").CurrentRegion.Copy Wks.Cells(r, "A")
             
End Sub

Hello vipvanilla,

You're welcome. Good job on the fix. It's very clever.