While dealing with a large Microsoft Excel dataset, sometimes we need to use the PasteSpecial command in Excel. We also can do that by applying VBA Macros. Today, in this article, we’ll learn three quick and suitable ways to use the VBA PasteSpecial formulas and formats in Excel effectively with appropriate illustrations.
VBA PasteSpecial Formulas and Formats (Quick View)
Sub Paste_Special()
Dim Rasel As Worksheet
Set Rasel = ThisWorkbook.Sheets("Formula and Format")
Rasel.Range("B5:D13").Copy
Rasel.Range("F5").PasteSpecial xlPasteAll
End Sub
How to Use VBA PasteSpecial Formulas and Formats in Excel: 3 Suitable Ways
Let’s say, we have a dataset that contains information about several Sales representatives in column B. The products are sold, and the revenue earned per month and per year by those Sales representatives have been given in columns C, D, and E respectively. We will apply the PasteSpecial formulas and formats with VBA Macros. Here’s an overview of the dataset for today’s task.
1. Perform the VBA PasteSpecial For Formulas
From our dataset, we will learn how to apply the VBA PasteSpecial for formulas. We can easily do that with a simple VBA code. Applying the VBA code to PasteSpecial formulas is time-saving also. Let’s follow the instructions below to learn!
Step 1:
- First of all, from your Developer tab, go to,
Developer → Visual Basic
- After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – VBA Paste Special will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,
Insert → Module
Step 2:
- Hence, VBA Paste Special module pops up. In the VBA Paste Special module, write down the below VBA code.
Sub Paste_Special()
Dim Rasel As Worksheet
Set Rasel = ThisWorkbook.Sheets("Formulas")
Rasel.Range("B4:E14").Copy
Rasel.Range("G4").PasteSpecial xlPasteFormulas
End Sub
- After that, run the VBA To do that, go to,
Run → Run Sub/UserForm
Step 3:
- Further, go back to your active worksheet, and you will be able to PasteSpecial formulas only in your active worksheet that has been given below screenshot.
Read More: How to Use VBA PasteSpecial to Keep Source Formatting in Excel
2. Use the VBA PasteSpecial For Format Only
After learning the VBA PasteSpecial for formulas, now, we’ll learn VBA PasteSpecial for Formats only from our dataset. Please follow the steps below to learn!
Steps:
- According to method 1, insert a new module and type the below VBA code to apply the PasteSpecial for format only. The VBA code is,
Sub Paste_Special()
Dim Rasel As Worksheet
Set Rasel = ThisWorkbook.Sheets("Formats")
Rasel.Range("B4:E14").Copy
Rasel.Range("G4").PasteSpecial xlPasteFormats
End Sub
- Hence, run the VBA To do that, go to,
Run → Run Sub/UserForm
- After that, go back to your active worksheet, and you will be able to PasteSpecial formats only in your active worksheet that has been given below screenshot.
Read More: VBA Paste Special to Copy Values and Formats in Excel
3. Apply the VBA PasteSpecial Formulas and Formats
Undoubtedly, applying the VBA code to PasteSpecial formulas and formats is an easy task. First, we will perform the VBA code to PasteSpecial formulas and formats in a single column, and then we will apply the code for multiple columns.
3.1 Use the VBA PasteSpecial Formulas and Formats in Single Column
From our dataset, we will perform the VBA code to PasteSpecial formulas and formats in a single column. Let’s follow the steps below.
Steps:
- First, place your cursor on the active sheet name, and press right-click on your Mouse. A window pops up. From that window, select the View Code option.
- After clicking on the View Code option, a VBA Paste Special module pops up. Write down the below code in that module.
Sub Paste_Special()
Dim Rasel As Worksheet
Set Rasel = ThisWorkbook.Sheets("Formula and Format")
Rasel.Range("C4:C14").Copy
Rasel.Range("G4").PasteSpecial xlPasteAll
End Sub
- After that, run the VBA To do that, go to,
Run → Run Sub/UserForm
- Hence, go back to your active worksheet, and you will be able to PasteSpecial formulas and formats in a single column G in your active worksheet that has been given below screenshot.
Read More: How to Use VBA to Paste Values Only with No Formatting in Excel
3.2 Apply the VBA PasteSpecial Formulas and Formats in Multiple Columns
After learning the VBA PasteSpecial command in a single column, we will apply the VBA PasteSpecial command in multiple columns from our dataset. Let’s follow the steps below to learn!
Step 1:
- To apply the VBA PasteSpecial command in multiple columns, insert a new module according to method 1. Now, type the below code in that module. The VBA code is,
Sub Paste_Special()
Dim Rasel As Worksheet
Set Rasel = ThisWorkbook.Sheets("Multiple Columns")
Rasel.Range("C4:E14").Copy
Rasel.Range("G4").PasteSpecial xlPasteAll
End Sub
- Hence, run the VBA To do that, go to,
Run → Run Sub/UserForm
Step 2:
- After completing the above process, you will be able to get your desired output which has been given in the below screenshot.
Things to Remember
👉 You can pop up the Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously.
👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,
File → Option → Customize Ribbon
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
I hope all of the suitable methods mentioned above to VBA Paste Special formulas and formats will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
Related Articles
- How to Paste From Clipboard to Excel Using VBA
- How to Copy Visible Cells Only without Header Using VBA
- Excel VBA to Copy Only Values to Destination
- Excel VBA: Copy Cell Value and Paste to Another Cell
- How to Copy Multiple Rows in Excel Using Macro
- Excel VBA: Copy Row If Cell Value Matches
- Copy and Paste Values to Next Empty Row with Excel VBA
- How to Autofilter and Copy Visible Rows with Excel VBA
