How to "transfer" row formatting to newly added rows?

Question

I am working on a "price estimation" Excel VBA project.

The basic layout of the page is:

(top) Billing information, date, company info,... [rows 1-13] -fixed
(middle) --> Bill of materials <-- (problem area) [row 14] - extendable
(bottom) Calculated price (total price) [row 15] - movable

Bill of material area starts at row 14 ("above" everything is "fixed"). In this line, I have some cells formatted with functions (vlookup and some basic multiplication - price*quantity). The functions are inserted directly in the cells in the workbook/worksheet.

"How do I keep the formatting from row 14 for all the rows inserted below it?"

I have code to:

open a dialog box,
open another excel workbook,
select the bill of materials from said workbook.

After the "range selection process" the computer calculates the number of rows needed in/on my form, it then:

creates space for the bill of materials,
inserts the new range (bill of materials) in my form/spreadsheet.

The code:

Private Sub cmdAddBOM_Click()

    Dim wkbCrntWorkBook As Workbook
    Dim wkbSourceBook As Workbook
    Dim rngSourceRange As Range
    Dim rngDestination As Range

    Dim iAreaCount As Integer 'row count variable

    Set wkbCrntWorkBook = ActiveWorkbook
    With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear
        .Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa"
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then
            Workbooks.Open .SelectedItems(1)
            Set wkbSourceBook = ActiveWorkbook

            Set rngSourceRange = Application.InputBox(prompt:="Select source range", Title:="Source Range", Default:="A1", Type:=8)

            wkbCrntWorkBook.Activate
            rngSourceRange.ClearFormats
            iAreaCount = rngSourceRange.Rows.Count
            '   MsgBox "The selection contains " & iAreaCount & " rows."

            Range("A14").EntireRow.Offset(0).Resize(iAreaCount).Insert Shift:=xlDown

            Set rngDestination = Application.InputBox(prompt:="Select destination cell", Title:="Select Destination", Default:="A14", Type:=8)

            rngSourceRange.Copy rngDestination

            wkbSourceBook.Close True

        End If
    End With

End Sub

Case 1:

Range("A14").EntireRow.Offset(0).Resize(iAreaCount).Insert Shift:=xlDown

The "Offset" property is set to "0".

For example: iAreaCount = 28

The result of this is that the formulas and functions which were previously in "Row14" are now in "Row42" and the bill of material was inserted "above" row 42 ("Row 14 got pushed down").

"Row42", is empty, the specific cells contain formulas and equations but there are no values.

(In other words: What was previously Row14 is moved down by the amount of iAreaCount and the new range is inserted above.)

Case 2:

Range("A14").EntireRow.Offset(1).Resize(iAreaCount).Insert Shift:=xlDown

The "Offset" property is set to "1".

For example: iAreaCount = 28

In this case, the "Row14" gets populated (values are present) and the formulas are left in place, but only for this row.
Also, I have an empty row at the bottom.

In both cases I have added 29 rows and not 28. I thought I would fix this with "Offset(0)".

Questions:

How to add the functions and equations which are in some cells in row 14 to all the corresponding cells in newly added rows (automatically).
How to, not get, an empty row at the bottom, after new range is added?

How to "transfer" row formatting to newly added rows?

0 Answers