Excel VBA

Copy & Paste in Excel VBA (copy, pastespecial, resize & offset)

This tutorial shows you how you can copy and paste with VBA.

We use the copy method as well as the pastespecial method.

The copy method in VBA, copies data, as well as formatting, formulas, comments etc.

With the pastespecial method, you have more control over your paste options.

For example, you can paste formatting only – or choose to paste values as well as number formatting.

I also show you how to use the resize property, so you can change the size of the current region before you copy.

This is good for cases where you want to copy the data but exclude the header.

Here we add in OFFSET as well.

Copy method

The syntax of the Copy method is: Copy([Destination]).

This copies all the contents of the source, including any formulas, comments, and formatting.

If the source range is fixed, this can be used: Range(“A4:E10”).Copy Range(“J4”).

This copies all the cells in the source range, A4:E10 and pastes it starting at cell J4.

To create a variable resized range that copies over any new rows added to the source, the CurrentRegion property is used.

Range(“A4”).CurrentRegion.Copy Range(“J4”)

PasteSpecial method

This is used to only copy the values, excluding the formatting, comments, and formulas.

The options for the PasteSpecial method can be shown after pressing SPACE after the PasteSpecial method.

  • Paste values
    • Range(“A4”).CurrentRegion.Copy
    • Range(“J20”).PasteSpecial xlPasteValues

  • Paste comments
    • Range(“J20”).PasteSpecial xlPasteComments

  • Paste values and number formats
    • Range(“J20”).PasteSpecial xlPasteValuesAndNumberFormats

TIP: To duplicate a row of code, highlight the row, hold down the CTRL button and drag the row over to where you want the duplicate row to be.

Resize property

This is helpful when copying a region excluding the headers.

The region can be offset one row down using the Offset property.

Range(“A4”).CurrentRegion.Offset(1,0)

This row will appear red initially since the formula is incomplete at this point.

To verify that the address is correct, test it in the Immediate Window:

?Range(“A4”).CurrentRegion.Offset(1,0).Address

Pressing ENTER will give the address of the new source after the offset has been done.

If having the extra row is an issue, an additional formula is used to exclude it using the resize property.

To use this: Resize([RowSize], [ColumnSize]).

Since the RowSize will be the actual number of rows excluding the header, we will count the region and deduct 1.

This allows it to be dynamic when additional rows are added.

Resize(Range(“A4”).CurrentRegion.Rows.Count-1)

Merging these into one becomes:

Range(“A4”).CurrentRegion.Offset(1,0).Resize(Range(“A4”).CurrentRegion.Rows.Count-1)

This can again be tested in the Immediate Window:

?Range(“A4”).CurrentRegion.Offset(1,0).Resize(Range(“A4”).CurrentRegion.Rows.Count-1).Address

Pressing ENTER will show that it now excludes the blank row after the current region.

The Copy method is then added.

Since the formula row is too long, this can also be broken down into two rows by using the _ separator:

Range(“A4”).CurrentRegion.Offset(1,0)._

Resize(Range(“A4”).CurrentRegion.Rows.Count-1).Copy

Range(“A20”).PasteSpecial xlPasteValuesAndNumberFormats

The dotted copy lines will be seen on the spreadsheet as if manually copying a cell.

To remove this, use: Application.CutCopyMode = False.

The destination where the Paste action was done last will be highlighted gray.

To remove this, jump to another cell by using: Range(“A1”).Select.

This makes cell A1 as the active cell as the final action.

Summary

Video

Feel free to Download the Workbook HERE.

Free Excel Download

TWEET THIS EXCEL VBA TECHNIQUE

Try it yourself

I hope you liked this tutorial. What did you think of it?

Do you have any questions?

In any case, give me an idea of how this worked out by leaving a comment below.

Unlock Excel VBA & Macros Course is here!

Now available on Udemy!

Save time. Achieve more. Over 50 Excel macro examples for download & useful VBA codes you can use for your work.

Redeem your coupon for 75% off below.

Get 75% off the course
Free Ebook

5 Comments

  1. Sandeep Kothari June 3, 2018 at 7:04 am - Reply

    Great tutorial. Pl allow me to download the workbook for practice.

    • Leila Gharani June 5, 2018 at 6:17 am - Reply

      It’s now available for download. Enjoy :)

  2. Malini Kothari June 3, 2018 at 7:12 am - Reply

    What is the fee for your entire VBA course?

  3. Malini Kothari June 4, 2018 at 4:17 pm - Reply

    Thanks. I will enrol for the course by tomorrow.

Leave A Comment

Share This