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]

Just throw an IF clause in there:

Sub addRows()


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
    
    If firstRow <> "" Then
        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
    End If
    
Next myLoop


End Sub