How to Use VBA PasteSpecial for Formulas and Formats in Excel

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.

Method 1 – Using the VBA PasteSpecial For Formulas

Step 1:

In the Developer tab, go to

Developer → Visual Basic

Perform the VBA PasteSpecial For Formulas

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

Insert → Module

Perform the VBA PasteSpecial For Formulas

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

Perform the VBA PasteSpecial For Formulas

Step 3:

Go back to your active worksheet.

This is the output.

Perform the VBA PasteSpecial For Formulas

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

Use the VBA PasteSpecial For Format Only

Run the VBA:

Run → Run Sub/UserForm

Go back to your active worksheet.

This is the output.

Use the VBA PasteSpecial For Format Only

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.

Use the VBA PasteSpecial Formulas and Formats in Single Column

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

Use the VBA PasteSpecial Formulas and Formats in Single Column

Run the VBA:

Run → Run Sub/UserForm

Go back to your active worksheet.

This is the output.

Use the VBA PasteSpecial Formulas and Formats in Single Column

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

Apply the VBA PasteSpecial Formulas and Formats in Multiple Columns