VBA - Insert Row based on Cell Value

[smitpau]

Hi,

I'd like to be able to create VBA which will insert a row based on a cell reference (for that row number) and will use spacing to insert the same row at say a spacing of 5.

Basically the issue is if a line is added to say a P&L (in this case Overheads), then for n number of companies each of there P&L's need a new line inserted so believe a VBA should be able to make this quicker.

Hopefully the example below is clear, I'm not sure if a Row Limit cell would have to be required so the spacing doesn't just continue to the end of the sheet which wouldn't be required.

Thanks for reading!

Book2
	A	B	C	D	E	F	G
1				VBA Inputs
2				Row	Spacing
3				9	5
4
5	P&L List		Before			After
6	Revenue		1	Company 1		1	Company 1
7	Cost of Sales		2	Revenue		2	Revenue
8	Overheads		3	Cost of Sales		3	Cost of Sales
9	Profit		4	Profit		4	Overheads
10			5			5	Profit
11			6	Company 2		6
12			7	Revenue		7	Company 2
13			8	Cost of Sales		8	Revenue
14			9	Profit		9	Cost of Sales
15						10	Overheads
16						11	Profit
Sheet1

Cell Formulas
Range	Formula
E3	E3	=C11-C6
D7:D8	D7	=A6
D9	D9	=A9
D12:D14	D12	=D7

 

[smitpau]

Thanks that works well except the copy for some reason is not like a normal copy paste as it does not keep the flow (if that makes sense) it's like a cut paste instead.

Example below of what I mean.

Line spacer macro.xlsm
	A	B	C	D	E	F	G
1	P&L List			VBA Inputs
2	Revenue			Row	Spacing
3	Cost of Sales			8	6
4	Overheads
5	Profit					1	Company 1
6						2	Revenue
7						3	Cost of Sales
8						3	Overheads
9						4	Overheads
10						5	Profit
11						6
12						7	Company 2
13						8	Revenue
14						8	Cost of Sales
15						9	Cost of Sales
16						10	Overheads
17						11	Profit
Sheet1

Cell Formulas
Range	Formula
E3	E3	=F12-F5
G6:G8	G6	=A2
G9:G10	G9	=A4
G13:G14,G16:G17	G13	=G6
G15	G15	=G7

 

[smitpau]

I believe it may need some sort of Autofill tweak as the copy is correct it's just the cells below are then out of order.

 

[mse330]

I'm not really sure if I understand your issue 100% but check the below to what I believe your issue is. I have added 1 more line in my code to highlight the inserted row in yellow to see things clearly. If you run the below code in the attached sample, it should insert mse330 before the word overheads correctly since I have already updated it in column A where the formulas are linked to. However, if you delete it from column A then re-run the code again, you'll notice that the new inserted row copied correctly from the above formula but the word overheads is still reading form the same cell, hence it appears to you as it is cut paste. Give it a try & let me know if my assumption is correct

Sub InsertRows()

Dim RowStart As Integer, Spacing As Integer, lRow As Long
RowStart = [D3]
Spacing = [E3]
lRow = Range("G" & Rows.Count).End(xlUp).Row 'Change column D to the column where your data is located

x = RowStart
Do While x <= lRow
    Rows(x).Insert
    Rows(x - 1).Copy Cells(x, 1)
    Cells(x, "G").Interior.Color = vbYellow
    x = x + Spacing
    lRow = lRow + 1
Loop

End Sub

Book1
	A	B	C	D	E	F	G
1	P&L List			VBA Inputs
2	Revenue			Row	Spacing
3	Cost of Sales			8	7
4	mse330
5	Overheads					1	Company 1
6	Profit					2	Revenue
7						3	Cost of Sales
8						4	Overheads
9						5	Profit
10						6
11						7	Company 2
12						8	Revenue
13						9	Cost of Sales
14						10	Overheads
15						11	Profit
16						12
17						13	Company 3
18						14	Revenue
19						15	Cost of Sales
20						16	Overheads
21						17	Profit
Sheet5

Cell Formulas
Range	Formula
G6:G7	G6	=A2
G8:G9	G8	=A5
G12:G15,G18:G21	G12	=G6

 

[smitpau]

This is what I get when I run it.

Yes you're right exactly as the rows below the inserted row are static so they don't realise to also reference the next cell (hence the duplicate references).

Book2
	A	B	C	D	E	F	G
1	P&L List			VBA Inputs
2	Revenue			Row	Spacing
3	Cost of Sales			8	6
4	mse330
5	Overheads					1	Company 1
6	Profit					2	Revenue
7						3	Cost of Sales
8						3	mse330
9						4	Overheads
10						5	Profit
11						6
12						7	Company 2
13						8	Revenue
14						8	Cost of Sales
15						9	Cost of Sales
16						10	Overheads
17						11	Profit
18						12
19						13	Company 3
20						13	Company 3
21						14	Revenue
22						15	Cost of Sales
23						16	Overheads
24						17	Profit
Sheet2

Cell Formulas
Range	Formula
E3	E3	=F12-F5
G6:G10	G6	=A2
G13:G14,G16:G17,G22:G24	G13	=G6
G15,G21	G15	=G7

 

[smitpau]

Sorry my mistake that does work and that's very helpful, ideally I meant for the spacing to be 6 in this example not 7 if the code could be tweaked for that please.

Book2
	A	B	C	D	E	F	G
1	P&L List			VBA Inputs
2	Revenue			Row	Spacing
3	Cost of Sales			8	7
4	mse330
5	Overheads					1	Company 1
6	Profit					2	Revenue
7						3	Cost of Sales
8						3	mse330
9						4	Overheads
10						5	Profit
11						6
12						7	Company 2
13						8	Revenue
14						9	Cost of Sales
15						9	mse330
16						10	Overheads
17						11	Profit
18						12
19						13	Company 3
20						14	Revenue
21						15	Cost of Sales
22						15	mse330
23						16	Overheads
24						17	Profit
Sheet2

Cell Formulas
Range	Formula
G6:G10	G6	=A2
G13:G17,G20:G24	G13	=G6

 

[smitpau]

That does everything I've asked for you've been a huge help (can just change Spacing to E3+1).

I can try to do this bit myself but thought I'd ask, say I would like to add multiple rows so D3 to say D24 were row inputs (not all of them would be necessarily used) would that be possible as part of the loop.

 

[smitpau]

I've made something like this, but it's an inelegant way of adding multiple row inputs.

Sub InsertRows()

Dim RowStart As Integer, RowN As Integer, Spacing As Integer, lRow As Long
RowStart = [D3]
RowN = [D4]
Spacing = [E3+1]
lRow = Range("G" & Rows.Count).End(xlUp).Row 'Change column D to the column where your data is located

x = RowStart
Do While x <= lRow
    Rows(x).Insert
    Rows(x - 1).Copy Cells(x, 1)
    x = x + Spacing
    lRow = lRow + 1
Loop

x = RowN
Do While x <= lRow
    Rows(x).Insert
    Rows(x - 1).Copy Cells(x, 1)
    x = x + Spacing + 1
    lRow = lRow + 1
Loop


End Sub

 

[mse330]

That does everything I've asked for you've been a huge help (can just change Spacing to E3+1).

Glad I was able to help to get what you initially needed. For your last requirement, it would be tricky since you've already inserted rows the first round/loop …