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
Perform the VBA PasteSpecial For Formulas

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

Perform the VBA PasteSpecial For Formulas

  • 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

Perform the VBA PasteSpecial For Formulas

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

Perform the VBA PasteSpecial For Formulas

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.

Perform the VBA PasteSpecial For Formulas

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

Use the VBA PasteSpecial For Format Only

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

Use the VBA PasteSpecial For Format Only

Read More: How to Apply VBA PasteSpecial and Keep Source Formatting in Excel


Similar Readings


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.

Use the VBA PasteSpecial Formulas and Formats in Single Column

  • 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

Use the VBA PasteSpecial Formulas and Formats in Single Column

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

Use the VBA PasteSpecial Formulas and Formats in Single Column

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

Apply the VBA PasteSpecial Formulas and Formats in Multiple Columns

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

Apply the VBA PasteSpecial Formulas and Formats in Multiple Columns

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

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo