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.

how to copy formulas containing relative references 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.

Dataset named Prime Number containing Prime Numbers, Square, and Double Square.

method

1. Use Paste Special Feature Using EXCEL VBA to Copy Formula with Relative Reference

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.

VBA PasteSpecial feature to copy formulas with relative references into another range of cells in Excel.

Code

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

🔨 Code Breakdown

👉  firstly, we set to work on the PasteSpecial worksheet writing Worksheets(“PasteSpecialt”).Activate.

👉  Secondly, Range(“C6:C10”).Copy dictates to copy the C6:C10 range.

👉  Thirdly, Range(“D6”).PasteSpecial Paste:=xlPasteFormulas delineates to paste the reference of formulas relatively in cell D6. Therefore it is pasted till D10 cells.

👉  Fourthly, disable the cut and copy mode writing Application.CutCopyMode = False. However, this line is not mandatory.

  • 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.

copying the formulas with relative reference, obtain the result using Excel VBA.

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

method

2. Apply Paste Special and Transpose Formula in VBA with Relative Reference

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

🔨 Code Breakdown

👉  Initially,, we set to work on the PasteSpecial worksheet writing Worksheets(“Transpose”).Activate.

👉  Secondly, Range(“C6:C10”).Copy delineates to copy the C6:C10 range.

👉  Thirdly, Range(“D6”).PasteSpecial Paste:=xlPasteFormulas dictates to paste the reference of formulas relatively in cell D6. Therefore it is pasted till D10 cells.

👉  Then, disable the cut and copy mode writing Application.CutCopyMode = False. However, this line is not mandatory.

👉  Next, copy the range B5:D10 and transpose them to H5 cell inputting  Range(“H5”).PasteSpecial Transpose:=True.

👉  Therefore, formulas insert with relative reference horizontally instead of vertically.

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

Transposing and copying the formulas with relative reference, obtaining the result using Excel VBA in 6 columns as well as 3 rows.

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

method

3. Copy Formula with Autofill Relative Cell Reference Offset in Excel VBA

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

🔨 Code Breakdown

👉  primarily, to select the data containing formulas, one needs to select range C6:C10 writing Range(“C6:C10”).Select.

👉  Then, disable the cut and copy mode to avoid malfunctioning applying Application.CutCopyMode = False.

👉  Next, Selection.AutoFill Destination:=Range(“C6:D10”), Type:=xlFillDefault implies autofill the range D6:D10 just like the C6:C10 range.

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

Auto-filling the cells containing relative reference that copy the formulas and achieve value of double square using Excel VBA.

📕 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

🔨 Code Breakdown

👉  Firstly, Range(“C6:C10”).Copy delineates to copy the C6:C10 range.

👉  Secondly, Range(“D6:D10”).PasteSpecial Paste:=xlPasteValues dictates to paste the values only relatively without formulas in the D6 to D10 cells.

👉  Then, disable the cut and copy mode writing Application.CutCopyMode = False.

👉  Next, select the range D6:D10 typing Range(“D6:D10”).Select.

👉  Further, Selection.RowHeight = 25 signals to set the row height by 25 units. On the other hand, Selection.ColumnWidth = 20 to set the column width by 20 units.

  • 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.

pasting the values only with formatted cells without copying formulas with relative cells using VBA.

📕 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

🔨 Code Breakdown

👉  Firstly, we set to work on the PasteSpecial worksheet writing Worksheets(“Transpose”).Activate.

👉  Secondly, Range(“C6:C10”).Copy delineates to copy the C6:C10 range.

👉  Thirdly, Range(“D6:D10”).PasteSpecial Paste:=xlPasteValues dictates to paste the values only relatively without formulas in the D6 to D10 cells.

👉  Then, disable the cut and copy mode writing Application.CutCopyMode = False.

👉  Next, Range(“D6:D10”).Select to select the D6:D10 range.

👉  After that, Selection.Interior.Color = vbYellow to set the interior cell color as yellow.

👉  Further, Selection.Font to work with font; whereas, .Color = vbRed is to set the font color red. .Size = 16 dictates to set font size by 16 units.

👉  Finally, to set the letters as Italic, write .Font.Italic = True and on the other hand, .Font.Bold = True to bold the letters.

  • 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.

copying the values only as well as formatting the cells.

📕 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

(Visited 26 times, 1 visits today)
MD Tanvir Rahman

MD Tanvir Rahman

Hello, I am Tanvir. Graduated from BUET. Had a year-long Experience at Operation of Vitacan Industries Ltd. I found Excel is the most unique software to reduce both time and paper. Now, I am an Excel enthusiast, love to play with data and publish content.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo