Excel VBA - Setting one-row worksheet range of relative formula to blank multi-row range, relative references skipping rows
I'm trying to create a macro for users of an Excel workbook template to:
1) add rows inside a named range,
2) move the last record in this named range to the top row in this range of new rows (to maintain the bounds of the named range and preserve the order of any user-input records), and
3) fill these new rows with formula from a master row.
Where I'm stuck is when the master row formula are set to this range of multiple blank rows, the relative references (after the first row) skip a row. For example, if the first row in this range is, say, Row 16, then:
Row 16 has a formula reference to A16,
Row 17 has that reference set to A18,
Row 18 has that reference set to A20, etc.
Below is the sub meant to perform this task:
Option Explicit
Public Sub AddRows( _
ByRef rrngInputFields As Range, _
ByRef rrngMasterRow As Range, _
ByVal intRowsToAdd As Long)
'Add rows to worksheet
'rrngInputFields is the named range that defines user input fields
'rrngMasterRow is the row that contains template formula
'intRowsToAdd is the number of rows to add, determined by the user
Dim rngRefRow As Range
Dim rngLastRecordOldRow As Range
Dim intLastRecordNewNo As Integer
Dim rngNewRows As Range
Dim rngLastRecordNewRow As Range
Dim intCountCol As Integer
'Set the last user record in the user input range
'New rows will be added above this to preserve the range
Set rngRefRow _
= rrngInputFields.Cells(rrngInputFields.Rows.Count, 1).EntireRow
'Set the last user record to later be moved to the top of the added rows
'This will maintain the order the user input their data
Set rngLastRecordOldRow = rngRefRow
intLastRecordNewNo = rngRefRow.Row
'Add the new rows
rngRefRow.Resize(intRowsToAdd).Insert
intCountCol = rrngMasterRow.Columns.Count
'Set the range of new rows that will receive the template formula and formatting
Set rngNewRows _
= ActiveSheet.Range(Cells(intLastRecordNewNo + 1, 1), _
Cells(intLastRecordNewNo + intRowsToAdd, intCountCol))
'Set the row to move the last user input record
Set rngLastRecordNewRow = ActiveSheet.Rows(intLastRecordNewNo)
'Move the last user record to the bottom of the existing records
rngLastRecordNewRow.FormulaR1C1 = rngLastRecordOldRow.FormulaR1C1
'Copy the master row, including all formulas and formatting, to the added rows
rngNewRows.FormulaR1C1 = rrngMasterRow.FormulaR1C1
End Sub
The last line is where the issue presents itself.
What would cause this skip in the relative reference?
