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.
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.
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.
๐ Read More: 9 Examples to Copy Formula from Above Cell Using VBA in Excel
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.
๐ Read More: How to Copy a Formula in Excel with Changing Cell References
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.
๐ 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.
๐ 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.
๐ 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