Macro to insert n rows in various worksheets

[AHACK]

Hi All, hope everyone had a good Christmas and NY.

My problem is that I have a large workbook with multiple sheets. At times users need to add 'n' rows at 20+ locations within the work book (eg sheet 1 has 2 locations, sheet 2 1 location etc). This is time consuming and prone to error, as people need to ensure they have added rows at every required sheet to ensure the cashflows line up.

I require two items:

Macro to insert x rows at various specific row locations across multiple sheets, dependent on a msg box request for number of rows from the user
As the rows are inserted it would also fill down only the formulas and formatting from the row immediately preceding the newly added row/s.

Second requirement, if possible would be if the macro could understand if rows have been added previously and update the starting points on each worksheet to take account of the previous row insertion. (if this is too much brain damage I could leave this out and mandate that users only add rows once as they are setting up the workbook for use)

(this is a property model that could have variable numbers of tenants, each with individual cashflows that need to be rolled up in various locations)

Apologies to all if this is asking alot.

Cheers

Hak

 

[svendiamond]

This could be done easily. The possibility of receiving a solution now lies in your ability to provide us with cell references, details like the sheet name, the amount of rows, and really just any general picture of what your data looks like.

 

[AHACK]

This could be done easily. The possibility of receiving a solution now lies in your ability to provide us with cell references, details like the sheet name, the amount of rows, and really just any general picture of what your data looks like.

Hi Sven, I have setup the work book with a worksheet to setup the various row positions etc. I thought this would be a good idea incase the worksheet changes. As it stops the need for people to access the VBA.

Example below. Each bold item (ex italic) are worksheets; each normal text item eg"Bottom" with a row number is the reference to the data area and row point for insertion of additional rows. My thought was that you create variables for each normal text items in VBA, the VBA variables are initially set by the original row numbers column. Once the Macro is run once, it outputs updated row numbers (ie Bottom - Original Row 13 + 10 rows in macro = Bottom - Updated Row 23). This way if the macro is run again it already has the updated starting points for the insertion of rows? Is this a dumb idea?

VBA Control Rows
			Original Row	Updated Row
Tenancy Sched Bottom		13
Physical
	Unit Hold Series		17
	Area Series		32
	Lease Type Series		52
	Vacancy		81
TS-Growth-Mkt
	GF Market Rental Profile		89
Property CFs
	Conracted Income		14
	Speculative Income 1st Term		29
	Speculative Income 2nd Term		44
	Speculative Income 3rd Term		59
	Recoveries (All Terms)		81
	Lease Fee		112
	Make Good 1		127
	Make Good 2		143
	Make Good 3		158
	Incentives 1		173
	Incentives 2		188
	Incentives 3		203

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[AHACK]

Sven, the data is generally numerical and formula based, some text. It is generally laid out across the page as a time series, IE a number of descriptive or input columns followed by a series of 120-240 monthly cashflows.

Sorry should have included that in the information previously submitted.

Cheers

 

[AHACK]

This could be done easily. The possibility of receiving a solution now lies in your ability to provide us with cell references, details like the sheet name, the amount of rows, and really just any general picture of what your data looks like.

Sven I have come up with the following to set the variable values (ie the row number of the spot where I would like the rows inserted).

Sub SetVarFromCell()
    Dim TenEnd As Long
    Dim PhyUnit As Long
    Dim PhyArea As Long
    Dim PhyLeaseT As Long
    Dim PhyVac As Long
    Dim TsgMkt As Long
    Dim PropCi As Long
    Dim PropSi1 As Long
    Dim PropSi2 As Long
    Dim PropSi3 As Long
    Dim PropLf As Long
    Dim PropMg1 As Long
    Dim PropMg2 As Long
    Dim PropMg3 As Long
    Dim PropIn1 As Long
    Dim PropIn2 As Long
    Dim PropIn3 As Long
   
      
TenEnd = Sheet25.Cells(8, "F").Value
PhyUnit = Sheet25.Cells(10, "F").Value
PhyArea = Sheet25.Cells(11, "F").Value
PhyLeaseT = Sheet25.Cells(12, "F").Value
PhyVac = Sheet25.Cells(13, "F").Value
TsgMkt = Sheet25.Cells(15, "F").Value
PropCi = Sheet25.Cells(17, "F").Value
PropSi1 = Sheet25.Cells(18, "F").Value
PropSi2 = Sheet25.Cells(19, "F").Value
PropSi3 = Sheet25.Cells(20, "F").Value
PropLf = Sheet25.Cells(21, "F").Value
PropMg1 = Sheet25.Cells(22, "F").Value
PropMg2 = Sheet25.Cells(23, "F").Value
PropMg3 = Sheet25.Cells(24, "F").Value
PropIn1 = Sheet25.Cells(25, "F").Value
PropIn2 = Sheet25.Cells(26, "F").Value
PropIn3 = Sheet25.Cells(27, "F").Value




End Sub

I presume that I can then feed this back into the next routine to set the row locations for the insertion of rows.

Cheers

 

[svendiamond]

Hate to be a pain, but I would really recommend you download and install the Mr Excel HTML Maker from this link: http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2545970

Follow the instructions to install it and use it to copy and paste your data into the forum. It will make it much easier to carry over from Excel and will look like this:

Book1
	I	J
1	Easily	Readable
2	Data1	123
3	Data2	456
Sheet1

Just looking at what you already posted, where did you get the 10 from, to add to the 13, for "bottom" ?

 

[AHACK]

Sorry Sven. First time I have really posted a problem. Normally I can find the answer.

Excel 2012

	C	D	E	F	G
5	VBA Control Rows
6				Original Row	Updated Row
7	Tenancy Sched
8		End		14
9	Physical
10		Unit Hold Series		17
11		Area Series		32
12		Lease Type Series		52
13		Vacancy		81
14	TS-Growth-Mkt
15		GF Market Rental Profile		89
16	Property CFs
17		Conracted Income		14
18		Speculative Income 1st Term		29
19		Speculative Income 2nd Term		44
20		Speculative Income 3rd Term		59
21		Recoveries (All Terms)		81
22		Lease Fee		112
23		Make Good 1		127
24		Make Good 2		143
25		Make Good 3		158
26		Incentives 1		173
27		Incentives 2		188
28		Incentives 3		203

<tbody>
</tbody>

AuditVB​

Sorry should have mentioned in my post. Using 10 was just assuming that I would create 10 new rows.

The Tenancy Schedule page looks like the following

Excel 2012

	C	D	E	F	G	H	I	J	K	L	M	N
2		Current Ten. Schedule / Contracted Income
3	#	Vacant? (1,0)	Unit # / Level	Start Unit	End Unit	Area Type	Area (SQM)	Tenant	Recov Type N/G/SG	Commence	Expiry	Current Rental (P.A)
4	1		L1		31-Dec-17	OFFICE	100.00	Test Tenant	G	1-Jan-15	31-Dec-17	50,000
5	2		L2			RETAIL	100.00			1-Jan-15	31-Dec-18
6	3		L3			PARKING	10.00			1-Jan-15	31-Dec-19
7	4		L4			STORAGE	1.00			1-Jan-15	31-Dec-20
8	5		L5			COMMS	1.00			1-Jan-15	31-Dec-21
9	6		L6			SIGNAGE	1.00			1-Jan-15	31-Dec-22
10	7		L7			OTHER	1.00			1-Jan-15	31-Dec-23
11	8		L8	31-Jan-18
12	9		L9
13	10		L10
14

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

Tensch​

 

[AHACK]

Hate to be a pain, but I would really recommend you download and install the Mr Excel HTML Maker from this link: http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2545970

Follow the instructions to install it and use it to copy and paste your data into the forum. It will make it much easier to carry over from Excel and will look like this:

	I	J
1	Easily	Readable
2	Data1	123
3	Data2	456

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

Sheet1​

Just looking at what you already posted, where did you get the 10 from, to add to the 13, for "bottom" ?

I got this far Sven but I am not very good at this.

Option Explicit
    
    Public TenEnd As Long
    Public PhyUnit As Long
    Public PhyArea As Long
    Public PhyLeaseT As Long
    Public PhyVac As Long
    Public TsgMkt As Long
    Public PropCi As Long
    Public PropSi1 As Long
    Public PropSi2 As Long
    Public PropSi3 As Long
    Public PropLf As Long
    Public PropMg1 As Long
    Public PropMg2 As Long
    Public PropMg3 As Long
    Public PropIn1 As Long
    Public PropIn2 As Long
    Public PropIn3 As Long
    


Function SetRowInsertPosition() As Long
           
TenEnd = Sheet25.Cells(8, "F").Value
PhyUnit = Sheet25.Cells(10, "F").Value
PhyArea = Sheet25.Cells(11, "F").Value
PhyLeaseT = Sheet25.Cells(12, "F").Value
PhyVac = Sheet25.Cells(13, "F").Value
TsgMkt = Sheet25.Cells(15, "F").Value
PropCi = Sheet25.Cells(17, "F").Value
PropSi1 = Sheet25.Cells(18, "F").Value
PropSi2 = Sheet25.Cells(19, "F").Value
PropSi3 = Sheet25.Cells(20, "F").Value
PropLf = Sheet25.Cells(21, "F").Value
PropMg1 = Sheet25.Cells(22, "F").Value
PropMg2 = Sheet25.Cells(23, "F").Value
PropMg3 = Sheet25.Cells(24, "F").Value
PropIn1 = Sheet25.Cells(25, "F").Value
PropIn2 = Sheet25.Cells(26, "F").Value
PropIn3 = Sheet25.Cells(27, "F").Value






End Function


Sub InsertRow()
     
    Dim Rng As Long
    Dim lngA As Long
    Dim lngB As Long
   
   Call SetRowInsertPosition
        
    Application.ScreenUpdating = False
     
     
        
    Rng = InputBox("Enter number of rows required.")
     
    If Rng = 0 Then Exit Sub
     
       
     
    Sheet5.Activate
        Rows(TenEnd).Resize(Rng).Insert
        
               
       


    Application.ScreenUpdating = True
    
   
     
End Sub

Seems to insert the rows for TenEnd ok, however none of the formatting is carried down. I also cant work out how to fill any information/calcs down either?

Pretty happy considering before this morning I had not written any VBA.

Thanks

 

[svendiamond]

I'm sorry, are you basically just trying to extend the range on sheet "Tensch" by X amount of rows? And fill down the formulas/formatting into these new rows? What would your expected results look like for entry "Conracted Income" in cell D17?

 

[AHACK]

I'm sorry, are you basically just trying to extend the range on sheet "Tensch" by X amount of rows? And fill down the formulas/formatting into these new rows? What would your expected results look like for entry "Conracted Income" in cell D17?

Morning Sven, the workbook has the following pages, all of which requires row insertion and filling down of the formula from above. (Pages requiring rows include; Tensch (tenancy schedule), Physical (indicators re vacancy etc), TS_Growth_Mkt (Series of Market Rents for each line on the tenancy schedule), Property CF's - This page requires rows to be inserted in at several different locations as can be seen on the Audit VB page highlighted previously.
The 'Original Row' reference matches the various rows on the worksheets where rows should be inserted.

See below for the Contacted Income section. I would expect that x rows would be added after row 31 and that formatting and formula would be copied down. (formula for O31 below). You will see that the formula refers to Tensch row 13, which is the last row of data on the tensch previously provided.

Excel 2012

	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
19	x	Conracted Income									Date	31-Jan-16	29-Feb-16	31-Mar-16
20		#	Tenant									1	2	3
21		#	Tenant
22		1	Test Tenant									4,167	4,167	4,167
23		2	0									0	0	0
24		3	0									0	0	0
25		4	0									0	0	0
26		5	0									0	0	0
27		6	0									0	0	0
28		7	0									0	0	0
29		8	0									0	0	0
30		9	0									0	0	0
31		10	0									0	0	0
32							Leave Blank for Insert Rows Macro

<tbody>
</tbody>

Property CFs​

Excel 2012

	O
31	0

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

Property CFs​

Worksheet Formulas Cell Formula O31 =+IFERROR(IF(AND(O$19>=Tensch!$L13,O$19<=Tensch!$M13),1/12,0)*IF(O$19<=Tensch!$R13,Tensch!$N13,IF(O$19<=Tensch!$T13,Tensch!$S13,IF(O$19<=Tensch!$V13,Tensch!$U13,IF(O$19<=Tensch!$X13,Tensch!$W13,IF(O$19<=Tensch!$Z13,Tensch!$Y13,IF(O$19<=Tensch!$AB13,Tensch!$AA13,IF(O$19<=Tensch!$AD13,Tensch!$AC13,IF(O$19<=Tensch!$AF13,Tensch!$AE13,IF(O$19<=Tensch!$AH13,Tensch!$AG13,IF(O$19<=Tensch!$AJ13,Tensch!$AI13,IF(AND(O$19>Tensch!$AJ13,O$19<EOMONTH(Tensch!$AJ13,12)),Tensch!$AK13,IF(AND(O$19>=EOMONTH(Tensch!$AJ13,12),MONTH(Tensch!$AJ13)=MONTH(O$19)),N31*12*((Tensch!$AK13/Tensch!$AI13)),N31*12))))))))))))*O16,0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>