Insert Row & copy and paste formulas in new row

[jjdame]

This is the current code I use to insert a new row under row 3:



End Sub
Sub sbCopyAndInsert()
Range("A4").EntireRow.Insert
End Sub


I want the shortcut key function to also Copy and Insert the Formulas (but not data) from Row 3 to the newly inserted row.

Please help with solution.

[June7]

So you don't want to do copy/insert paste of entire row, just specific cells?

Use macro recorder to generate some basic code then customize for your requirements.

[georgiboy]

Maybe the below will help:

Sub sbCopyAndInsert()
Rows(4).Insert
Rows(4).Formula = Rows(3).Formula
Rows(4).SpecialCells(xlCellTypeConstants).ClearContents
End Sub

[jjdame]

So you don't want to do copy/insert paste of entire row, just specific cells?

Use macro recorder to generate some basic code then customize for your requirements.

Thank you. I will check out Macro Recorder

[jjdame]

The code you posted did help but still missing one piece. I now have:

Sub sbInsertingRows()
Range("A4").EntireRow.Insert
Rows(4).Formula = Rows(3).Formula
End Sub

The hotkey now will insert the row, and formulas. The last issue I face is the calculation for the new rows. It is transferring the formulas but it is trying to calculate data based on the rows cells it cloned. I need it to calculate data based on the new rows cells. For example when I use the hotkey and insert the new row. It clones the formulas from row 3. This is correct. It also clones that calculation data. In row 3, cell H3 is =SUM(F3-G3) and I3 is =H3/F3. After hot key is used in row 4 cell H4 is ALSO =SUM(F3-G3) and I4 is ALSO =H3/F3. I would like those calculations to correspond with the row they are in. So when user ads multiple rows the result would be:
~ Row 3, cell H3 is =SUM(F3-G3) and I3 is =H3/F3
~ Row 4, cell H4 is =SUM(F4-G4) and I4 is =H4/F4
~ Row 5, cell H5 is =SUM(F5-G5) and I5 is =H5/F5
~ Row 6, cell H6 is =SUM(F6-G6) and I6 is =H6/F6
.......

I attached the excel file too.

Thanks
John Dame

[georgiboy]

Maybe the below amendment:

Sub sbCopyAndInsert()
Rows(4).Insert
Rows(3).AutoFill Rows("3:4")
Rows(4).SpecialCells(xlCellTypeConstants).ClearContents
End Sub