Inserting rowsbelow using VBA and pasting row above into inserted cells

[laurars]

Hello,
I am new to VBA. Right now I am really struggling to do this.
I need to create a macro that reads the value of a column, (column F), and depending on the value of the cells in the column, it will insert rows below. If the value in column K is greater than one, it will insert n-1 rows below. After inserting those rows, it will paste the above row in the new inserted rows.
I attached a sample table for clarification. In red are the cells from the original sheet. Below the cells with red text, you can see the cells that have been inserted and their pasted content with blue text.
I would really appreciate your help. I have been breaking my head and cannot get it to work. I will be working with thousands of lines of data so your help will be greatly appreciated.
I hope this is clear
Thank you!!

	A	B	C	D	E	F
1	part#					Quantity
2	11					2
3	11					2
4	22					1
5	33					3
6	33					3
7	33					3

<tbody>
</tbody>

 

[hiker95]

laurars,

Welcome to the MrExcel forum.

What version of Excel are you using?

If the value in column K

You are not showing column K?


Can you post a screenshot of the raw data worksheet, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[hiker95]

laurars,

Now I understand - there is no column K - just a typo.

Sample raw data:

Excel 2007
	A	B	C	D	E	F
1	part#	B	C	D	E	Quantity
2	11					2
3	22					1
4	33					3
5
6
7
8
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E	F
1	part#	B	C	D	E	Quantity
2	11					2
3	11					2
4	22					1
5	33					3
6	33					3
7	33					3
8
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub InsertRows()
' hiker95, 09/28/2013
' http://www.mrexcel.com/forum/excel-questions/729652-inserting-rowsbelow-using-visual-basic-applications-pasting-row-above-into-inserted-cells.html
Dim r As Long, lr As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 6).End(xlUp).Row
For r = lr To 2 Step -1
  If Cells(r, 6) > 1 Then
    Rows(r + 1).Resize(Cells(r, 6) - 1).Insert
    Rows(r).Copy Rows(r + 1).Resize(Cells(r, 6) - 1)
  End If
Next r
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the InsertRows macro.

 

[laurars]

Hello Hiker95,

Thank you very very much for your response!!
The macro works perfectly. I tested even with modifications and it works. You have no idea of how much help this is to me. Thanks a lot!!!!!!!!

laurars,

Now I understand - there is no column K - just a typo.

Sample raw data:

Excel 2007

	A	B	C	D	E	F
1	part#	B	C	D	E	Quantity
2	11					2
3	22					1
4	33					3
5
6
7
8

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

After the macro:

Excel 2007

	A	B	C	D	E	F
1	part#	B	C	D	E	Quantity
2	11					2
3	11					2
4	22					1
5	33					3
6	33					3
7	33					3
8

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub InsertRows()
' hiker95, 09/28/2013
' http://www.mrexcel.com/forum/excel-questions/729652-inserting-rowsbelow-using-visual-basic-applications-pasting-row-above-into-inserted-cells.html
Dim r As Long, lr As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 6).End(xlUp).Row
For r = lr To 2 Step -1
  If Cells(r, 6) > 1 Then
    Rows(r + 1).Resize(Cells(r, 6) - 1).Insert
    Rows(r).Copy Rows(r + 1).Resize(Cells(r, 6) - 1)
  End If
Next r
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the InsertRows macro.

 

[hiker95]

laurars,

Please do not quote entire replies from your helper. When quoting follow these guidelines:
1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
2. Quote ONLY the specific part of the post that is relevant - - not the entire post.

This will keep thread clutter to a minimum and make the discussion easier to follow.


Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[Kt999]

  If Cells(r, 6) > 1 Then
    Rows(r + 1).Resize(Cells(r, 6) - 1).Insert
    Rows(r).Copy Rows(r + 1).Resize(Cells(r, 6) - 1)

Hi, I am getting runtime error 13 type mismatch on the line starting with Rows(r+1)
any clue why?

 

[hiker95]

Kt999,

Hi, I am getting runtime error 13 type mismatch on the line starting with Rows(r+1)
any clue why?

I am going to assume that your raw data structure, and, results, are not the same as the originator of this thread, laurars.


If that is the case, then I would suggest:


That you start a new thread for your question and be sure to give it a search friendly title that accurately describes your need.

In your New Thread include:
1. the version of Excel you are using
2. Are you using a PC or a Mac?
3. a screenshot, NOT a picture/graphic, of the raw data, and, worksheet name
4. a screenshot, NOT a picture/graphic, of the results (manually formatted by you for the results you are looking for)


To post a small screen shot try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


Or, you can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.


Then send me a Private Message, with a link to your New Thread, and, I will have a look.