Using a macro to add a new row onto a table pulling values from other worksheet-CodePudding

I'm trying to get a macro to work which adds a new row into a table, inserting values from another worksheet.

So far I have either managed to get the macro to insert the first row, however subsequent rows then overwrite the previous. Currently, I have put the 'Line' variable in which produces some different behaviour where the first row inserts 3 times, then subsequent rows are inserted a few rows further down.

Extract below:

Sub UpdateItemIndex(ItemName As String)

    Dim ItemIndexSheet As Worksheet
    Dim ItemIndexTable As ListObject
    Dim NewEntryRow As ListRow
    Dim NewEntryRowIndex As Integer
    Dim RangeName As String
    
    Set ItemIndexSheet = Sheets("Item Index")
    ItemIndexSheet.Select
    
    
    Set ItemIndexTable = ItemIndexSheet.ListObjects(1)
    'Remove any filters
    ItemIndexTable.AutoFilter.ShowAllData
    
    Set NewEntryRow = ItemIndexTable.ListRows.Add
    
    NewEntryRowIndex = NewEntryRow.Index
    
    'Line = Range("NextItemIndex").Value
    Line = Worksheets("Resources").Cells(2, 5)
    
    NewEntryRow.Range(Line, "A").Value = ItemName
       
    RangeName = ItemName & "!" & "ItemTitle"
    NewEntryRow.Range(Line, "B").Formula = "=" & ItemName & "!" & Range(RangeName).Address
    
    RangeName = ItemName & "!" & "ItemStatus"
    NewEntryRow.Range(Line, "C").Formula = "=" & ItemName & "!" & Range(RangeName).Address
       
End Sub

Prior to the 'Line' variable, the new row entry commands were:

NewEntryRow.Range(1, "B").Formula = "=" & ItemName & "!" & Range(RangeName).Address

This is part of a larger macro where a click button generates a new worksheet and a table updates with the new values.

I'm new to VBA so would be grateful for any indication as to what the problem is!

CodePudding user response：

As you already have NewEntryRow - which represents the new row - you definitly don't need the index of it. NewEntryRow has only one row!

This should work:

With NewEntryRow
    .Range(1, "A") = ItemName
    .Range(1, "B") = "=" & ItemName & "!ItemTitle"
    .Range(1, "C") = "=" & ItemName & "!ItemStatus"
End With

As you "named" the Title and Status cells you can refer to them by name. You don't need the address.

--- update --- You need to add following lines to turn automatic formula updating off:

Application.AutoCorrect.AutoFillFormulasInLists = False
    With NewEntryRow
        .Range(1, "A") = ItemName 
        .Range(1, "B") = "=" & ItemName & "!ItemTitle"
        .Range(1, "C") = "=" & ItemName & "!ItemStatus"
    End With
Application.AutoCorrect.AutoFillFormulasInLists = True

This will result in a lot of red signs - as Excel expects a formula to be consistend within the table

If you can live with an INDIRECT formula you would avoid this:

    With NewEntryRow
        .Range(1, "A") = ItemName 
        .Range(1, "B") = "=INDIRECT([@" & ItemIndexTable.ListColumns(1).Name & "] & ""!ItemTitle"")"
        .Range(1, "C") = "=INDIRECT([@" & ItemIndexTable.ListColumns(1).Name & "] & ""!ItemStatus"")"
    End With