VBA to insert rows

mc136355 · Aug 22, 2019

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 · Aug 24, 2019

How about this:

Code:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1107749a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1107749-vba-insert-rows.html[/COLOR][/I]

    [COLOR=Royalblue]Dim[/COLOR] tbl [COLOR=Royalblue]As[/COLOR] ListObject
    [COLOR=Royalblue]Dim[/COLOR] x [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
   
    Application.ScreenUpdating = [COLOR=Royalblue]False[/COLOR]
    [COLOR=Royalblue]Set[/COLOR] tbl = ActiveSheet.ListObjects([COLOR=brown]"medline"[/COLOR])
    x = Range([COLOR=brown]"A1"[/COLOR]).Value
    [COLOR=Royalblue]With[/COLOR] tbl
    .DataBodyRange.Rows([COLOR=brown]"1:"[/COLOR] & x).Insert
    .DataBodyRange.Rows(x + [COLOR=crimson]1[/COLOR]).Copy .DataBodyRange.Rows([COLOR=brown]"1:"[/COLOR] & x)
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
    Application.ScreenUpdating = [COLOR=Royalblue]True[/COLOR]
   
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

igold · Aug 24, 2019

Akuni - your code fails as it does not copy the formula from row 1 to the newly inserted rows.

And now as I retest my code, I am getting the same result. When I tested it before I posted it, it appeared to work correctly. Very weird.

igold · Aug 24, 2019

I think perhaps the way to do this is to convert the Table to a Range, do the work, and then convert the Range back to a Table....

Akuini · Aug 24, 2019

Hm, I tested my code again, and it worked, the formula are copied to the new rows. And I also tested your code, and it worked as well.

igold · Aug 24, 2019

Have you tried it with formulas that are not absolute references...

Every time I run it the formula from the original Row 1 autofills upwards changing the original non absolute reference...

Akuini · Aug 24, 2019

igold said:
Have you tried it with formulas that are not absolute references...

Every time I run it the formula from the original Row 1 autofills upwards changing the original non absolute reference...

I don't quite understand, isn't that what should have happened?
I mean the first row will be the same before & after the code runs.
Wait, are you saying that now the code is working again? I mean the formula is copied to the new rows?

igold · Aug 24, 2019

Say for example you have decided to add 2 rows (the value in Cell A1). The original formula in DataBodyRange(1,1) is "=R6+S6". According to Post #1 , the OP wants identical rows of data to be created.

After the code runs the formula that now appears in DataBodyRange(1,1) is =R4+S4 and the cell right below that is =R5+S5. The original formula resides in the next cell down (Row 3 of the table, as we have added 2 new rows).

That is not how I am interpreting the OP.

I hope this makes sense.

Additionally, at this point, even if this method works for the OP, I have now created a challenge for myself to make the inserted rows contain the exact formula as was the original with no reference changes.

Akuini · Aug 24, 2019

Hm, I got different result.
Here's an example:
The formula in B4 is '=A4*2' & it stays the same after the macro run.

BEFORE:
Excel 2013 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][th]

B

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1

[/td][td=bgcolor:#FFFF00]

2

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3

[/td][td=bgcolor:#5B9BD5]xx[/td][td=bgcolor:#5B9BD5]yy[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4

[/td][td=bgcolor:#DDEBF7]

1

[/td][td=bgcolor:#DDEBF7]

=A4*2

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5

[/td][td]

2

[/td][td]

=A5*2

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6

[/td][td=bgcolor:#DDEBF7]

3

[/td][td=bgcolor:#DDEBF7]

=A6*2

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]

AFTER:
Excel 2013 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][th]

B

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1

[/td][td=bgcolor:#FFFF00]

2

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3

[/td][td=bgcolor:#5B9BD5]xx[/td][td=bgcolor:#5B9BD5]yy[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4

[/td][td=bgcolor:#DDEBF7]

1

[/td][td=bgcolor:#DDEBF7]

=A4*2

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5

[/td][td]

1

[/td][td]

=A5*2

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6

[/td][td=bgcolor:#DDEBF7]

1

[/td][td=bgcolor:#DDEBF7]

=A6*2

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7

[/td][td]

2

[/td][td]

=A7*2

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8

[/td][td=bgcolor:#DDEBF7]

3

[/td][td=bgcolor:#DDEBF7]

=A8*2

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]

mc136355 · Aug 24, 2019

wrong post sorry

mc136355 · Aug 24, 2019

thought i was in the wrong thread sorry

Hi igold
The issue with this one is that the code line

.ListRows(1).Range.PasteSpecial xlPasteAll

copies into the first row but moves everything across by one column

then second row and moves everything across by two columns and so on.

thanks for sharing your wisdom and i am really grateful for your help MC

VBA to insert rows

mc136355

New Member

Akuini

Well-known Member

Excel Facts

igold

Well-known Member

igold

Well-known Member

Akuini

Well-known Member

igold

Well-known Member

Akuini

Well-known Member

igold

Well-known Member

Akuini

Well-known Member

mc136355

New Member

mc136355

New Member

Similar threads

Forum statistics

Share this page

VBA to insert rows

New Member

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock