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

 

[igold]

Yes, using the data from your post #18 , I get the same results. Now change your formula in cell B4 to =R6*S6, B5 =R7*S7 and tell me what you get...

 

[mc136355]

<colgroup><col style="mso-width-source:userset;mso-width-alt:3537;width:76pt" width="101"> <col style="mso-width-source:userset;mso-width-alt:3840;width:83pt" width="110"> <col style="mso-width-source:userset;mso-width-alt:3607;width:78pt" width="103"> <col style="mso-width-source:userset;mso-width-alt:2583;width:56pt" width="74"> </colgroup><tbody>
[TD="class: xl70, width: 101"]DATE[/TD]
[TD="class: xl71, width: 110"]PRESC NO[/TD]
[TD="class: xl71, width: 103"]CUSTOMER[/TD]
[TD="class: xl71, width: 74"]SITE[/TD]

[TD="class: xl69"]#REF![/TD]
[TD="class: xl72"]0[/TD]
[TD="class: xl73"]43678[/TD]
[TD="class: xl73"]123456789[/TD]

[TD="class: xl69"]00/01/1900[/TD]
[TD="class: xl72"]43678[/TD]
[TD="class: xl73"]123456789[/TD]
[TD="class: xl73"]CustA
[/TD]

[TD="class: xl69"]01/08/2019[/TD]
[TD="class: xl72"]123456789
[/TD]
[TD="class: xl73"]CustA
[/TD]
[TD="class: xl73"]SiteA
[/TD]

</tbody>

here is whats happening MC

 

[igold]

<tbody>
[TD="class: xl70"]DATE[/TD]
[TD="class: xl71"]PRESC NO[/TD]
[TD="class: xl71, width: 103"]CUSTOMER[/TD]
[TD="class: xl71, width: 74"]SITE[/TD]

[TD="class: xl69"]#REF![/TD]
[TD="class: xl72"]0[/TD]
[TD="class: xl73"]43678[/TD]
[TD="class: xl73"]123456789[/TD]

[TD="class: xl69"]00/01/1900[/TD]
[TD="class: xl72"]43678[/TD]
[TD="class: xl73"]123456789[/TD]
[TD="class: xl73"]CustA[/TD]

[TD="class: xl69"]01/08/2019[/TD]
[TD="class: xl72"]123456789[/TD]
[TD="class: xl73"]CustA[/TD]
[TD="class: xl73"]SiteA[/TD]

</tbody>

here is whats happening MC

Both my code and Akuini's codes returns the date correctly. Is the date the result of a formula or is it static.

 

[mc136355]

Hi

i apologise the date is a formula but i realise it needs to be absolute ref. i have =C2 should be =$C$2 OOps MC

 

[igold]

Are any of the codes working for you...


@Akuini - the fact that the OP was getting the date error without an absolute reference, reinforces what I was experiencing with the formula autofilling with changed references

 

[mc136355]

Before code

<colgroup><col style="mso-width-source:userset;mso-width-alt:4142;width:178pt" width="237"> </colgroup><tbody>
[TD="class: xl69, width: 237"]COUNT
[/TD]

[TD="class: xl70"]=COUNTIF(F$15:F15,F15)
[/TD]

</tbody>

After code

<colgroup><col style="mso-width-source:userset;mso-width-alt:4142;width:178pt" width="237"> </colgroup><tbody>
[TD="class: xl69, width: 237"]COUNT[/TD]

[TD="class: xl70"]=COUNTIF(F15:F$16,F15)[/TD]

[TD="class: xl70"]=COUNTIF(F$16:F16,F16)[/TD]

[TD="class: xl70"]=COUNTIF(F$15:F17,F17)[/TD]

</tbody>

This is the only problem i find. the countif just numbers the rows 1 to whatever. This is getting mixed up. MC

 

[Akuini]

Yes, using the data from your post #18 , I get the same results. Now change your formula in cell B4 to =R6*S6, B5 =R7*S7 and tell me what you get...

If column R:S is part of the table then formula in B4 stays the same, but if it isn't then the formula changes to =R4*S4

 

[mc136355]

Akuini the code that you have provided seams to resolve this issue. At the moment i cant seam to find any further issue. Thanks MC

 

[igold]

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.

 

[Akuini]

Akuini the code that you have provided seams to resolve this issue. At the moment i cant seam to find any further issue. Thanks MC

Are you sure?
Actually I find my code produce the issue as you described at post #26
Please try this one:

Sub a1107749c()
'https://www.mrexcel.com/forum/excel-questions/1107749-vba-insert-rows.html

    Dim x As Long
    
    Application.ScreenUpdating = False
    x = Range("A1").Value

    With ActiveSheet.ListObjects("medline")
        .DataBodyRange.Rows("2:" & x + 1).Insert
        .DataBodyRange.Rows(1).Copy .DataBodyRange.Rows("2:" & x + 1)
    End With
    
    Application.ScreenUpdating = True
    
End Sub