How to Use VBA PasteSpecial for Formulas and Formats in Excel (3 Ways)

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

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

3 Suitable Ways to VBA PasteSpecial Formulas and Formats

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

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: VBA Paste Special to Copy Values and Formats in Excel (9 Examples)

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: How to Apply VBA PasteSpecial and Keep Source Formatting in Excel

Similar Readings

• How to Copy Unique Values to Another Worksheet in Excel (5 Methods)
• Copy Merged and Filtered Cells in Excel (4 Methods)
• How to Copy Rows in Excel (4 Easy Ways)
• Copy and Paste Thousands of Rows in Excel (3 Ways)‌
• How to Use Excel VBA to Paste Range into Email Body (3 Methods)

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: Difference Between Paste and Paste Special 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 have been given in the below screenshot.

Read More: How to Copy and Paste Exact Formatting in Excel(Quick 6 Methods)

Things to Remember

👉 You can pop up 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

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

• VBA Code to Compare Two Excel Sheets and Copy Differences
• Excel VBA: Copy Cell Value and Paste to Another Cell
• How to Disable Copy and Paste in Excel without Macros (With 2 Criteria)
• Excel VBA: Copy Range to Another Workbook
• How to Paste From Clipboard to Excel Using VBA
• Copy Excluding Hidden Rows in Excel (4 Easy Methods)
• How to Autofilter and Copy Visible Rows with Excel VBA