VBA to insert rows

[mc136355]

Hi ive searched google to try and find answer to my problem but there are similar questions but dont know how to manipulate the vba to suit me.

My problem is i need to achieve the following
i enter a value into A1 e.g 10 and it would then goto the start of my table data say A16 and then copy the row of data 10 times creating 10 identical rows of data. Any help on this would be appreciated.

thanks MC

 

[Akuini]

That is the answer then, as my formulas were referencing cells outside of the table.

However for my own benefit I am trying to write a solution that will have the desired result with references to non-table cells.

It's an interesting case, let us know if you managed to get the answer.

 

[igold]

As convoluted as it is, I believe this will add the rows to the table and not change relative references.

Sub InstTblRows()


    Dim tbl As ListObject
    Dim ct As Long, c As Long, trws As Long, r As Long
    Dim temtab1, temtab2
    Dim rng As Range, P As Range
    Dim fc As String
    
    Set tbl = ActiveSheet.ListObjects("medline")
    fc = tbl.DataBodyRange(1, 1).Address(0, 0)
    ct = ActiveSheet.Range("A1").Value + 1
    trws = tbl.ListRows.Count
    Set P = tbl.DataBodyRange
    temtab1 = P.Formula
    ReDim temtab2(1 To ct + trws, 1 To tbl.ListColumns.Count)
    
    For r = 1 To ct
        For c = 1 To tbl.ListColumns.Count
            temtab2(r, c) = temtab1(1, c)
        Next
    Next
    For r = ct + 1 To UBound(temtab2, 1) - 1
        For c = 1 To tbl.ListColumns.Count
            temtab2(r, c) = temtab1(r - ct + 1, c)
        Next
    Next
    
    tbl.Unlist
    Range(fc).Offset(-1, 0).Resize(UBound(temtab2, 1), UBound(temtab2, 2)).Select
    Range(fc).Resize(UBound(temtab2, 1) - 1, UBound(temtab2, 2)) = temtab2
    ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = "medline"
   
End Sub

 

[mc136355]

Hi Akuini both codes produce the same result which is what i need formulas are copied as expected. Thanks MC

 

[Akuini]

Hi Akuini both codes produce the same result which is what i need formulas are copied as expected. Thanks MC

You're welcome, glad to help, & thanks for the feedback.
But I think my last code is more reliable in most situation, so it's better to use that.