How to Use VBA PasteSpecial for Formulas and Formats in Excel – 3 Methods

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

The dataset contains information about Sales representatives,  products sold, and the revenue per month and per year.

Apply PasteSpecial formulas and formats with VBA Macros:

Method 1 – Using the VBA PasteSpecial For Formulas

Step 1:

• In the Developer tab, go to

Developer → Visual Basic

• In Microsoft Visual Basic for Applications – VBA Paste Special, insert a module to enter the VBA code. Go to:

Insert → Module

Step 2:

• In the VBA Paste Special module, enter the 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

• Run the VBA:

Run → Run Sub/UserForm

Step 3:

• Go back to your active worksheet.

This is the output.

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

Method 2 – Using the VBA PasteSpecial For Format Only

Steps:

• Follow the steps in Method 1 to insert a new module.
• Enter the VBA code.

Sub Paste_Special()
Dim Rasel As Worksheet
Set Rasel = ThisWorkbook.Sheets("Formats")
Rasel.Range("B4:E14").Copy
Rasel.Range("G4").PasteSpecial xlPasteFormats
End Sub

• Run the VBA:

Run → Run Sub/UserForm

• Go back to your active worksheet.

This is the output.

Read More: VBA Paste Special to Copy Values and Formats in Excel

Method 3 – Applying the VBA PasteSpecial Formulas and Formats

3.1 Use the VBA PasteSpecial Formulas and Formats in a Single Column

Steps:

• Place your cursor in the active sheet name, and right-click.
• Select View Code.

• In the VBA Paste Special module, enter the code.

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

• Run the VBA:

Run → Run Sub/UserForm

• Go back to your active worksheet.

This is the output.

Read More: How to Use VBA to Paste Values Only with No Formatting in Excel

3.2 Apply the VBA PasteSpecial Formulas and Formats to Multiple Columns

Step 1:

• Insert a new module.
• Enter the code.

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

• Run the VBA:

Run → Run Sub/UserForm

Step 2:

• Go back to your active worksheet.

This is the output.

Things to Remember

Open the Microsoft Visual Basic for Applications window by pressing Alt + F11.

If the  Developer tab is not visible on the ribbon, enable it:

File → Option → Customize Ribbon

Download Practice Workbook

Download the practice workbook.

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