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]

I'm sorry, but what does the value "14" mean for the "Contracted Income" "original row"???

The 'Original Row' reference matches the various rows on the worksheets where rows should be inserted.

But in your example of the Contracted Income sheet, you don't even show row 14 or mention it all... is that the row you want insert above?

Filling down the formulas would be something like:

Range("E31:Q" & lastRow).FillDown

 

[AHACK]

I'm sorry, but what does the value "14" mean for the "Contracted Income" "original row"???



But in your example of the Contracted Income sheet, you don't even show row 14 or mention it all... is that the row you want insert above?

Filling down the formulas would be something like:

Range("E31:Q" & lastRow).FillDown

So sorry Sven, in the example Contracted Income should read Row 32 as per the screen shot not 14. (Really sorry, have been working through 2 work books whilst I try and get the Macro working. The first workbook is 32, whilst the second has Contracted Income ending at 14, which is why I want to use an excel sheet to pass the row number to the macro. That way I can easily amend the excel workbook and update the macro without delving into VBA)

Sorry to muck you about.

 

[AHACK]

So I have managed to get to the following.

Excel 2012

	C	D	E	F	G
VBA Control Rows

<tbody>
[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #44546A"]PAGE[/TD]
[TD="bgcolor: #44546A"]SECTION[/TD]
[TD="bgcolor: #44546A, align: right"][/TD]
[TD="bgcolor: #44546A"]ORIG ROW[/TD]
[TD="bgcolor: #44546A"]UPDATE ROW[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFFFF"]Tenancy Sched[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"]End[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]29[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FFFFFF"]Physical[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FFFFFF"]TS-Growth-Mkt[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"]GF Market Rental Profile[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]54[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FFFFFF"]Property CFs[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"]Conracted Income[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]32[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"]Speculative Income 1st Term[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]46[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"]Speculative Income 2nd Term[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]61[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"]Speculative Income 3rd Term[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"]Recoveries (All Terms)[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]83[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"]Lease Fee[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]114[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"]Make Good 1[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]129[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"]Make Good 2[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]145[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"]Make Good 3[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"]Incentives 1[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"]Incentives 2[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"]Incentives 3[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]

</tbody>

AuditVB​

The following code sets the various points where rows need to be inserted based on the values in the table above.

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

The following code inserts the number of rows as per input box and then copies all formulas and formats down. However it only works for the Tenancy Schedule Page (sheet5).
I dont know how to update it to work across sheets or to work multiple times on certain sheets (obviously need to insert rows from the bottom of the sheet first, otherwise the row references will change).

Sub InsertRow()     
    Dim RowNum As Long
    Dim SourceRange As Long
    
   
        Call SetRowInsertPosition
        
            Application.ScreenUpdating = False
         
        
     RowNum = InputBox("Enter number of rows required.")
        If RowNum = 0 Then Exit Sub
     
       
     
        Sheet5.Activate
            Rows(TenEnd).Resize(RowNum).Insert
            ActiveCell.Offset(-1, 1).EntireRow.Copy
            ActiveCell.Resize(RowNum, 1).EntireRow.PasteSpecial Paste:=xlPasteFormats
            ActiveCell.Resize(RowNum, 1).EntireRow.PasteSpecial Paste:=xlPasteFormulas
            Application.CutCopyMode = False
   
               


    Application.ScreenUpdating = True
    
   
     
End Sub

I am getting close thanks to your help.

 

[svendiamond]

I don't think I can be of much more help without actually viewing your sheet. If you want, upload it to Fast-Files, or DropBox or something and I'll take a look.

 

[svendiamond]

Without diving too deep into your sheet, its purposes, functions, etc. I would offer this advice. Instead of:

        Sheet5.Activate
            Rows(TenEnd).Resize(RowNum).Insert
            ActiveCell.Offset(-1, 1).EntireRow.Copy
            ActiveCell.Resize(RowNum, 1).EntireRow.PasteSpecial Paste:=xlPasteFormats
            ActiveCell.Resize(RowNum, 1).EntireRow.PasteSpecial Paste:=xlPasteFormulas

You could try:

        Sheet5.Activate
            Rows(TenEnd).Resize(RowNum).Insert
            Rows(TenEnd).Offset(-1, 0).Copy _
                Rows(TenEnd).Resize(RowNum, 1)

Does that help?

 

[svendiamond]

And if you want to loop through different sheets... you should learn about loops. Here is a tutorial I recommend: https://www.youtube.com/watch?v=JyWrLH7monI&list=PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5&index=18

Also you should set up your AuditVB sheet to line up with your sheet names. Then you could just look for the sheet name in column D, and match it up with the row number listed in column F, rather than declaring a million variables and having to go back to each one. This way you could just loop through the whole thing at once. Something like:

For myLoop = 8 to 26 'data on AuditVB sheet

mySheet = Cells(myLoop,"D").Value 'if column D were sheet names
rowToInsert = Cells(myLoop,"F").Value

Sheets(mySheet).Activate
Rows(rowToInsert).Resize(RowNum).Insert
Rows(rowToInsert).Offset(-1,0).Copy

Next myLoop

Etc, etc etc... something like that.

 

[AHACK]

Hi I just tried your updated code and it seems to work perfectly.

So your code inserts the rows
It then uses Rows(TenEnd) -1 to get the last row with calcs to pull calc and formats
Does the final statement set the number of rows to copy down?

Its much simpler than mine. Thanks allot.

I will look at the next code and watch the video.

Should I be using an array to store information on the sheetcode and row number (eg the data set from the AuditVB sheet?)

 

[AHACK]

Hi Sven I have updated the AuditVB page to include a reference to the sheet code number. I would rather use this so that if the name of the sheet changes, we dont affect the VBA.

Excel 2012
	A	B	C	D	E	F	G
5			VBA Control Rows
6	ARR INDEX	CODE	PAGE	SECTION		ROW NO	SHEET CODE
7
8	1	END	Tenancy Sched	End		14	5
9	2		Physical
10	3	GFM	TS-Growth-Mkt	GF Market Rental Profile		55	13
11	4	CI	Property CFs	Conracted Income		32	19
12	5	SI1	Property CFs	Speculative Income 1st Term		47	19
13	6	SI2	Property CFs	Speculative Income 2nd Term		62	19
14	7	SI3	Property CFs	Speculative Income 3rd Term			19
15	8	R	Property CFs	Recoveries (All Terms)		84	19
16	9	LF	Property CFs	Lease Fee		115	19
17	10	MG1	Property CFs	Make Good 1		131	19
18	11	MG2	Property CFs	Make Good 2		146	19
19	12	MG3	Property CFs	Make Good 3			19
20	13	I1	Property CFs	Incentives 1		161	19
21	14	I2	Property CFs	Incentives 2			19
22	15	I3	Property CFs	Incentives 3			19
23	16	EA	Property CFs	Expiry Allowance		206	19
AuditVB

 

[svendiamond]

The sheet code number is not working... When I do Sheets(5).Activate it activates the "Input>>" sheet which is actually called "Sheet4" - so you may not want to go by the sheet item/key number like that. Otherwise I've got a loop that should work:

Sub addRows()


'Sub to insert x rows throughout the work book


Dim rowsToAdd As Long, shtCode As Integer, firstRow As Integer, myLoop As Integer
    rowsToAdd = InputBox("Enter number of rows required.")
            
For myLoop = 8 To 23
    shtCode = Sheets("AuditVB").Cells(myLoop, "G").Value
    firstRow = Sheets("AuditVB").Cells(myLoop, "F").Value
        With Sheets(shtCode) 'incorrect sheet code numbers so this won't work, you can use "With Sheets("tensch")" here to test
            .Activate
                .Rows(firstRow).Resize(rowsToAdd).Insert
                .Rows(firstRow).Offset(-1, 0).Copy _
                    Rows(firstRow).Resize(rowsToAdd, 1)
                
        End With
Next myLoop


End Sub

This would only work if the sheet code numbers were accurate.

 

[AHACK]

Thanks allot Sven.

You are right the sheet code number used above is use the sheet index number rather than the codename. Thats why input is coming back in your program.

One final question. How would I go about trapping errors in the excel table? EG in the example above the Physical line has now row number or sheet number. When I run the entire loop it throws errors every time there is no row number or sheet name (code). I am sure its an if then that needs to go in there but I cant seem to get it to work.

You sir are a life saver..

Thanks allot.