WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Paste Special

VBA Paste Special

Similar to worksheet when we copy a data and paste it on a different cell range we have a paste special method which allows us to paste the data as itself or only the formulas or only the values and same fashion we can use Paste Special in VBA using the range property method as follows range. paste special() providing the type we want in the brackets.

Paste Special in VBA

Paste Special in excel serves in many ways in our daily work. Using paste special, we can do many more things than the usual ones. Copy & paste is there everywhere in the computer world. But paste special is the advanced thing in excel.

Like regular excel paste special in VBA too, we have paste special method to paste the copied data. Copying the things in excel is not a strange thing for excel users, they copy, paste, and most of the time, they use paste special to serve their purpose in many ways.

VBA PasteSpecial

In regular excel, paste includes many options like paste only values, paste formulas, paste formats and etc.…

Paste Special

Paste special have to Paste, Operation, Skip Blanks, and Transpose like this in VBA too. We have all the parameters with Paste Special method.

The Formula of Paste Special in VBA

Below is the Formula for Paste Special in VBA

VBA PasteSpecial

Paste Special is available with VBA Range object because after copying the data, we will be pasting it in the cell range, so paste special method is available with range object.

Paste Type: After copying the data, how do you want to paste. Whether you want to paste values, formulas, formats, validation, etc.. Below is the complete list of options available under Paste Type.

Option of paste type

Paste Special Operation: While pasting, do you want to perform any kind of operations like add, subtract, division, multiplication, or none.

Paste special operation

  • [Skip Blanks]: If you want to skip blanks, then you can choose TRUE or FALSE.
  • [Transpose]: If you want to transpose the data, then you can choose TRUE or FALSE.

Examples of Paste Special in Excel VBA

The following are the examples of paste special in VBA.

You can download this VBA Paste Special Template here – VBA Paste Special Template

Example #1 – Paste only Values using VBA PasteSpecial Function

In the first example, we will perform pasting only values using paste special. Assume below is the data you have in the sheet name called Sales Data.

VBA PasteSpecial Example 1

Now we will perform the task of copy & paste using several paste special methods. Follow the below steps.

Step 1: Create a macro name first.

Step 2: First, copy the range A1 to D14 from the sheet name “Sales Data.” To copy the range, apply the below code.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code: 

Range("A1:D14").Copy

VBA PasteSpecial Example 1-1

Step 3: After copying the data, we will be pasting the values from G1 to J14. First, reference the range.

Code:

Range ("G1:J14")

VBA PasteSpecial Example 1-2

Step 4: After selecting the range, we need to paste. So put a dot (.) and select Paste Special method.

Code:

Sub PasteSpecial_Example1()

    Range("A1:D14").Copy
    Range("G1:J14").PasteSpecial

End Sub

VBA PasteSpecial Example 1-3

Step 5: From the drop-down list, select the option “xlPasteValues.”

Code:

Sub PasteSpecial_Example1()

   Range("A1:D14").Copy
   Range("G1:J14").PasteSpecial xlPasteValues

End Sub

VBA PasteSpecial Example 1-4

Step 6: Now run this code using the F5 key or manually and see what happens.

VBA PasteSpecial Example 1-5

So our code copied the data from A1 to D14 and pasted from G1 to J14 as values.

It has performed the task of shortcut excel key in worksheet ALT + E + S + V.

Shortcut key to paste

Example #2 – Paste All using VBA PasteSpecial

Now we will see what happens if we perform the task of xlPasteAll.

Code:

Sub PasteSpecial_Example2()

   Range("A1:D14").Copy
   Range("G1:J14").PasteSpecial xlPasteAll

End Sub

Now, if you run this code manually through the run option or by pressing the F5 key, we will have as it is data.

VBA PasteSpecial Example 1-8

Example #3 – Paste Formats using VBA PasteSpecial Function

Now we will see how to paste only formats. The Below code would do the job for us.

Code:

Sub PasteSpecial_Example3()

   Range("A1:D14").Copy
   Range("G1:J14").PasteSpecial xlPasteFormats

End Sub

VBA PasteSpecial Example 1-10

If you run this code using the F5 key or manually, we will get the only format of the copied range, nothing else.

VBA PasteSpecial Example 1-11

Example #4 – Paste Column Width using VBA Paste Special

Now we will see how to paste only column width from the copied range. For this, I have increased the column width for one of my data columns.

VBA PasteSpecial Example 1-13

Apply the below code it will paste only the column width of the copied range.

Code:

Sub PasteSpecial_Example3()

 Range("A1:D14").Copy
 Range("G1:J14").PasteSpecial xlPasteColumnWidths

End Sub

VBA PasteSpecial Example 1-14

Run this code and see the difference in the column width.

VBA PasteSpecial Example 1-15

Now we can see Sales column width has been increased to the column width of our copied range column.

Example #5 – Copy the Data from One Sheet to Another Sheet using VBA Paste Special Option

We have seen how to copy and paste the data on the same sheet. Now we will how to paste from one sheet to another sheet.

Step 1: Before we select the range, we need to tell from which sheet we need to select the data.

Code:

Sub PasteSpecial_Example5()

   Worksheets ("Sales Data")

End Sub

Example 2

Step 2: After selecting the sheet by its name, then we need to select the range in that sheet. The copy it.

Code:

Sub PasteSpecial_Example5()

  Worksheets("Sales Data").Range("A1:D14").Copy

End Sub

Example 2-1

The above code says in the sheet name “Sales Data” copy the Range, (“A1:D14”)

Step 3: Since we are pasting it in a different sheet, we need to select the sheet by its name.

Code:

Sub PasteSpecial_Example5()

  Worksheets("Sales Data").Range("A1:D14").Copy
  Worksheets ("Month Sheet")

End Sub

Example 2-2

Step 4: Now, in the sheet “Month Sheet,” select the range.

Code:

Sub PasteSpecial_Example5()

  Worksheets("Sales Data").Range("A1:D14").Copy
  Worksheets("Month Sheet").Range ("A1:D14")

End Sub

Example 2-3

Step 5: Using Paste special, we will be pasting values and format.

Code:

Sub PasteSpecial_Example5()

  Worksheets("Sales Data").Range("A1:D14").Copy
  Worksheets("Month Sheet").Range("A1:D14").PasteSpecial xlPasteValuesAndNumberFormats

End Sub

Example 2-4

Step 6: We are not only pasting values and format using VBA Paste Special, but we are pasting it as TRANSPOSE as well.

Code:

Sub PasteSpecial_Example5()

Worksheets("Sales Data").Range("A1:D14").Copy
Worksheets("Month Sheet").Range("A1:D14").PasteSpecial xlPasteValuesAndNumberFormats, Transpose:=True

End Sub

Example 2-5

Now run this code. It will copy and transpose the data to the “Month Sheet.”

Example 2-6

Things to Remember About Excel VBA PasteSpecial Function

  • If you want to skip blanks, you need to enter the argument as TRUE by default. It takes FALSE.
  • If you want to transpose the data, we need to select the transpose as TRUE.
  • We can perform only one paste special at a time.

Recommended Articles

This has been a guide to Excel VBA PasteSpecial. Here we learn the top 5 ways to use VBA PasteSpecial Function along with practical examples and downloadable VBA codes. Below are some useful excel articles related to VBA –

  • Record VBA Macros
  • VBA Paste
  • VBA Code in Excel Function
  • How to Transpose in VBA?
1 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Paste Special Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More