3 Ways to Copy Formula with Relative Reference Using Excel VBA

Writing down the formulas every time is tedious and dilatory. What if you write down a formula using cell reference and auto-filling the cells, doesn’t it sound crazy? Yes, Writing down a formula in a cell, MS Excel gives you the opportunity to fill the rest automatically using the Fill handle. In contrast, when you are more than a dilettante in Excel VBA, you may try VBA programming to auto-fill or copy a formula into other cells containing relative references in Excel. In this article, we will learn to copy the formula with relative reference using VBA in Excel.

📁 Download Excel File

To practice please download the Excel file from here:

Learn to Copy Formula with Relative Reference Using VBA in Excel with These 3 Approaches

In this article, we are going to expound on 3 approaches to copy the formula with relative reference using MS Excel VBA. Following that, we consider a dataset named Prime number containing the heading Prime Number, Square, and Double Square. Dataset also has 4 columns as well as 10 rows. To construct the methods, we will learn the use of Paste Special, Transpose, and Autofill features using VBA Macro. So, let’s get started.

To copy formulas while maintaining relative reference is essential to any user. Simple copy-paste methods only copy the data keeping the previous relative references. Here comes the Paste Special feature. The paste Special feature can be a savior to pasting the copied data containing cell references. Please follow the steps.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, open the desired worksheet.
• Then, go to the Developer menu from the Top Ribbon and get the Visual Basic feature.
• Thus, a box named Microsoft Visual Basic for Application appears. From the box, select the sheet from Left Side Ribbon.
• Next, insert the VBA code and hit on the Run icon to execute the VBA code.

Code

Sub Use_PasteSpecial_to_Copy_Formulas()
Worksheets("PasteSpecial").Activate
Range("C6:C10").Copy
Range("D6").PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
End Sub

• Therefore, we obtain the results containing relative references. Cell C6 is the square of cell B6 whereas D6 is the square of the C6 cell.

📕 Read More: 9 Examples to Copy Formula from Above Cell Using VBA in Excel

Not only copying the formula containing relative references but also transposing the cells containing relative references is possible. Likely a TRANSPOSE function, one can transpose the cells including relative reference within a second. Please check the steps.

⬇️⬇️ STEPS ⬇️⬇️

• Like the previous method, open the respective worksheet.
• Then, Select the Sheet Name from the Right Side Ribbon.
• Next, insert the following VBA code and click on the Run icon to execute.

Code

Sub Use_PasteSpecial_and_Transpose_to_Copy_Formulas()
Worksheets("Transpose").Activate
Range("C6:C10").Copy
Range("D6").PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
Range("B5:D10").Copy
Range("H5").PasteSpecial Transpose:=True
End Sub

• Finally, data of the B5:D10 range transposes to H5:M7 automatically. Relative references are also well maintained.

📕 Read More: How to Copy a Formula in Excel with Changing Cell References

Manually to fill automatically, we need to pull down the Fill Handle from the edge of a cell or double clicking on the Fill handle. But by writing a simple VBA code, users can also fill the cells automatically without any demur. Please check out the necessary steps.

⬇️⬇️ STEPS ⬇️⬇️

• Again, open the respective worksheet.
• Then, Select the sheet name Offset from the Left Side Ribbon where the code is written and execution will take place in the Offset worksheet only.
• Next, write the following code and hit the Run icon to proceed.

Code

Sub Copy_Formula_via_Autofil()
Range("C6:C10").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("C6:D10"), Type:=xlFillDefault
End Sub

• Therefore, obtain the result in the D6:D10 range including relative cell references which is the square of data of column C.

📕 Read More: 6 Ways to Copy a Formula Across Multiple Rows in Excel

Paste Values, Formats and Columns Widths with Excel VBA

Suppose, you need to paste values only along with the formatting of row height and column width. Writing a simple VBA code, you will be able to set the row height as well as column height within a second. Please check out the steps below.

⬇️⬇️ STEPS ⬇️⬇️

• Once again, open the respective worksheet where you are determined to store and execute the VBA code.
• After that, insert the following VBA code and hit the Run icon to execute the code.

Code

Sub Paste_values_only_and_format_row_columns()
Range("C6:C10").Select
Range("C6:C10").Copy
Range("D6:D10").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("D6:D10").Select
With Selection.RowHeight = 25
End With
With Selection.ColumnWidth = 20
End With
End Sub

• Therefore, we achieve the cells of D6 to D10 formatted with a Row height of 25 units as well as a Column width of 20 units.

📕 Read More: 9 Tricks to Copy Formula in Excel Without Dragging

Copy and Paste Values Only with Formatting Cells in Excel VBA

Copying the values only from other cells, one can format the cells with font size, interior color, etc. Generally, when we set a cell as Italic and Bold font including a suitable font and interior color, MS Excel generates a VBA code in the back end. Why don’t we write a VBA code of our own? Let’s check out the steps below.

⬇️⬇️ STEPS ⬇️⬇️

• Likely previous methods, open the respective worksheet where you are desired to store and execute the VBA code.
• Now, write down the following code in the VBA module of the respective worksheet and click on the Run icon to execute.

Code

Sub paste_only_values_and_formatting()
Range("C6:C10").Select
Range("C6:C10").Copy
Range("D6:D10").PasteSpecial Paste:=xlPastevalues
Application.CutCopyMode = False
Range("D6:D10").Select
With Selection.Interior
.Color = vbYellow
End With
With Selection.Font
.Color = vbRed
.Size = 16
End With
With Selection
.Font.Italic = True
.Font.Bold = True
End With
End Sub

• Therefore, we get the results in column D under Formatted Values with Red, Bold, and Italic font style as well as 16 font size and yellow colored Interior cell.

📕 Read More: 4 Quick Ways to Copy Formula Down with Shortcut in Excel

📄 Important Notes

🖊️  Enable the Developer menu first to execute VBA code.

🖊️  Calculate step by step while using VBA Macro otherwise malfunction may take place.

🖊️  For any disability of execution, use Debug Step Into to observe step by step.

📝 Takeaways from This Article

📌  Copy and Paste formulas with VBA Macro.

📌  Copy Values only instead of formulas using VBA Macro.

📌  Auto-filling of cells including relative references via Excel VBA.

📌  Formatting and copying values as well as formulas utilizing Excel VBA code.

Conclusion

In this article, we expound on 3 catchy approaches to copy formulas with relative reference in Excel VBA Macro. I hope you enjoyed your learning and will be able to copy formulas executing VBA code. Any suggestions, as well as queries, are appreciated. Don’t hesitate to leave your thoughts in the comment section. For better understanding and new knowledge, don’t forget to visit www.ExcelDen.com.

Related Articles

• How to Copy Excel Sheet to Another Workbook with Formulas
• 3 Quick Ways to Copy Formula and Paste as Text in Excel
• How to Copy Formula Down in Excel Without Incrementing