This is an overview.
The dataset shows mathematical results in column B. The formula behind the results for each cell is shown in Column D. If you click on the cells in Column B, you will see the operation in the formula bar.
- Cell B5 -> 6 = 4+2
- Cell B6 -> 2 = 4-2
- Cell B7 -> 8 = 4*2
- Cell B8 -> 2 = 4/2
Method 1- Embed VBA to a Copy Cell Above with the Formula from the First Column in Excel
Steps:
- Press Alt + F11 or go to Developer -> Visual Basic to open Visual Basic Editor.
- Click Insert -> Module.
- Enter the following code in the code window.
Sub CopySingleCellWithFormula()
Dim iBook As Workbook
Dim iSheet As Worksheet
Set iBook = ThisWorkbook
Set iSheet = Sheets("Single Column")
With iSheet
If IsEmpty(Range("B5").Offset(1, 0)) Then
Range("B5").Copy Range("B5").Offset(1, 0)
Else
Range("B5").End(xlDown).Copy Range("B5").End(xlDown).Offset(1, 0)
End If
End With
End Sub
The code will copy B8 and paste it in B9 with the formula.
- Press F5 or select Run -> Run Sub/UserForm. You can also click the Play icon to run the macro.
This is the output.
Read More: How to Copy Formula in Excel Without Dragging
Method 2 – Using a Macro to Insert User-Defined Copied Rows with the Formula
Steps:
- Press Alt + F11 or go to Developer -> Visual Basic to open Visual Basic Editor.
- Click Insert -> Module.
- Enter the following code in the code window.
Sub CopyEntireRowWithFormula()
Dim iSheet As Worksheet
Dim iRows As Long
Dim iLastColumn As Long
Dim iFirstRow As Long
Set iSheet = ThisWorkbook.Worksheets("Entire Rows")
iFirstRow = InputBox("Enter Row Number to Insert New Rows from")
iRows = InputBox("Enter Number of Rows to Insert")
If iFirstRow = 0 Or iRows = 0 Then Exit Sub
Debug.Print iFirstRow
IngA = iSheet.Cells(5, iSheet.Columns.Count).End(xlToLeft).Column
For i = 1 To iRows
iSheet.Range("A" & (iFirstRow + i)).EntireRow.Insert
iSheet.Range("A" & (iFirstRow + i) & ":BB" & (iFirstRow + i)).Formula = iSheet.Range("A" & iFirstRow & ":BB" & iFirstRow).Formula
Next
End Sub
- Press F5 or select Run -> Run Sub/UserForm. You can also click the Play icon to run the macro.
- In the input box, enter the row number to insert copied rows. Here, row 6.
- Click OK.
- In the next input, enter the number of rows you want to copy. Here, 3 copies of row 6.
- Click OK.
This is the output.
Row number 6 is copied and pasted with the formula 3 times in rows 7, 8 and 9.
Read More: How to Copy Formula Down Without Incrementing in Excel
Method 3 – Applying VBA to Copy a Cell from Above with the Formula and User-Defined Intervals
To copy B7 and paste it 3 rows below.
Steps:
- Press Alt + F11 or go to Developer -> Visual Basic to open Visual Basic Editor.
- Click Insert -> Module.
- Enter the following code in the code window.
Sub InsertRowsWithFormulaAfterInterval()
iNumber = Int(InputBox("Enter the Interval Numbers"))
Count = 1
For i = iNumber To Selection.Rows.Count - 1 Step iNumber
Selection.Cells(i + Count, 1).EntireRow.Insert
Count = Count + 1
Next i
Count = Count - 1
Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(ActiveCell.Row, ActiveCell.Column)).Copy
Range(Cells(ActiveCell.Row + iNumber, ActiveCell.Column), Cells(ActiveCell.Row + iNumber, ActiveCell.Column)).PasteSpecial xlPasteFormulas
For i = 2 To Count
Range(Cells(ActiveCell.Row + iNumber + 1, ActiveCell.Column), Cells(ActiveCell.Row + iNumber + 1, ActiveCell.Column)).PasteSpecial xlPasteFormulas
Next i
Application.CutCopyMode = False
End Sub
- Run the code.
- In the input box, enter the number of intervals. Here 3.
- Click OK.
This is the output.
B7 is copied with the formula after 3 intervals (rows) in the same column.
Read More: How to Copy Formula and Paste as Text in Excel
Method 4 – Applying a Macro Code to Duplicate the Entire Last Row to the Row Below with the Formula
Steps:
- Press Alt + F11 or go to Developer -> Visual Basic to open Visual Basic Editor.
- Click Insert -> Module.
- Enter the following code in the code window.
Sub CopyEntireLastRow()
Dim iRow As Long
iRow = Cells(Rows.Count, "B").End(xlUp).Row
Rows(iRow).Copy
Rows(iRow).Offset(1, 0).EntireRow.PasteSpecial Paste:=xlPasteFormats
Rows(iRow).Offset(1, 0).EntireRow.PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
End Sub
- Run the code.
This is the output.
If you click a copied cell, it shows the formula in the formula bar.
Read More: How to Copy Formula to Another Sheet in Excel
Method 5 – Using a Macro to Copy the Formula from the Cell Above Starting from the Last Cell
Steps:
- Press Alt + F11 or go to Developer -> Visual Basic to open Visual Basic Editor.
- Click Insert -> Module.
- Enter the following code in the code window.
Sub CopySelectedCellFromAbove()
'To go to the last cell
Range("B5").Select
Selection.End(xlDown).Select
LastCell = [B65536].End(xlUp).Offset(-1, 0).Address
Range(LastCell).Select
'To enter new line
Selection.EntireRow.Insert Shift:=xlUp, CopyOrigin:=xlFormatFromLeftOrAbove
'To copy formula from the cell above
Dim rng As Range
For Each rng In Selection
If (rng.Value = "") Then
rng.Offset(-1, 0).Copy Destination:=rng
End If
Next rng
End SubThe VBA code will start counting from the last cell (B8). When it reaches the cell above the last cell (B7), it inserts a new row. The rest of the cells below are pushed below. The previous B7 becomes B8 and B8 becomes B9. The code copies B6 with the formula.
- Run the code.
This is the output.
Read More: How to Copy and Paste Formulas from One Workbook to Another in Excel
Method 6 – Copy All Rows from Above with Formulas with VBA Macro in Excel
Steps:
- Press Alt + F11 or go to Developer -> Visual Basic to open Visual Basic Editor.
- Click Insert -> Module.
- Enter the following code in the code window.
Sub CopyAllRowsFromAboveWithFormulas()
Dim iSource As Range
Dim iTarget As Range
Dim iNum As Long
Set iSource = ActiveSheet.Range("B4:D8") 'Range with formula to copy
iNum = ActiveCell.Row
'Macro will run only if active cell is between columns B and D
If ActiveCell.Column >= 2 And ActiveCell.Column <= 4 Then
Rows(iNum).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Set iTarget = ActiveSheet.Range("B" & iNum & ":D" & iNum) 'Range to paste
iSource.Copy iTarget
End If
End Sub
- Run the code.
This is the output.
Related Content: [Fixed] Excel Not Copying Formulas, Only Values (4 Solutions)
Method 7 – Clearing Existing Data and Copy the Formula from Above with a Macro in Excel
Steps:
- Press Alt + F11 or go to Developer -> Visual Basic to open Visual Basic Editor.
- Click Insert -> Module.
- Enter the following code in the code window.
Sub ClearAndCopyFromAbove()
Dim Output
If ActiveCell.Row <> 1 Then 'Will return errors if active cell is in the first row
Output = MsgBox("Are you sure to copy formulas from above row?", vbYesNo)
If Output = vbYes Then
ActiveCell.Offset(-1, 0).EntireRow.Copy
ActiveCell.EntireRow.PasteSpecial Paste:=xlPasteFormats
ActiveCell.EntireRow.PasteSpecial Paste:=xlPasteFormulas
On Error Resume Next
'Next line will throw errors if no constants, otherwise skip errors
ActiveCell.EntireRow.Cells.SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0 'Returns to error capturing line
ActiveCell.Select 'Removes selection from the entire row
Application.CutCopyMode = False
End If
Else
MsgBox "Row unavailable from above selection. Nothing to copy from row above."
End If
End Sub
B6 is the active cell.
- Run the code.
- In the message box, If you don’t have the correct active cell, click No and go back to the dataset. If the correct cell is selected, Click Yes.
Previous data was cleared from B6 along with the formula and replaced with the value in B5.
Related Content: Excel VBA to Copy Formula with Relative Reference
Method 8. Embed VBA to Copy the Formula from a Cell Above and Shift the Rest of the Data Down
Insert a new cell in B6, copy the cell above: B5 with the formula and paste it into B6.
Steps:
- Press Alt + F11 or go to Developer -> Visual Basic to open Visual Basic Editor.
- Click Insert -> Module.
- Enter the following code in the code window.
Sub CopyAndShiftDown()
Range("B6").Insert Shift:=xlShiftDown
Range("B6").FillDown
End Sub
- Run the code.
This is the output.
Method 9 – Using VBA to Copy Multiple Cells with the Formula from Above and Shift the Rest of the Data Down
Insert 3 cells after B6 and fill them with the value and formula in B6.
Steps:
- Press Alt + F11 or go to Developer -> Visual Basic to open Visual Basic Editor.
- Click Insert -> Module.
- Enter the following code in the code window.
Sub CopyAndShiftDownMultiple()
Set iRng = Range("B6")
iRng.Copy
iRng.Offset(1).Insert Shift:=xlDown
iRng.Copy
iRng.Offset(1).Insert Shift:=xlDown
iRng.Copy
iRng.Offset(1).Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub- Run the code.
B6 is copied 3 times into the cells below (B7, B8 and B9), containing the formula.
Method 10 – Using VBA to Have Multiple Copies of a Selected Range with Formulas in the Cells Below
Create 10 copies of B8 with the formula in the 10 cells below.
Steps:
- Press Alt + F11 or go to Developer -> Visual Basic to open Visual Basic Editor.
- Click Insert -> Module.
- Enter the following code in the code window.
Sub CopyManyWithFormula()
Dim iNum As Long
iNum = 10 'You can customize this number
Set iRng = Range("B8")
iRng.Copy
iRng.Resize(iNum, 1).PasteSpecial (xlFormulas)
End Sub
- Run the code.
This is the output.
Download Workbook
Download the free practice Excel workbook.
Related Articles
<< Go Back to Copy Formula in Excel | Excel Formulas | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
