Excel VBA – Copying & Pasting

Cut, copy, paste must be the bane of some people’s working lives. Whether you use right clicks or keyboard shortcuts to do it, chances are this is one of the most common repetitive actions you do at work, whether it is in Excel, Word or pretty much any application you care to think of.

A large proportion of what we copy and paste will be one off events, but those of you who do monthly reporting, or any type of reporting for that matter, you probably find yourself doing a lot of copying and pasting of the same data, month after month.

In this blog I will show you some simple code that will automate the whole process. I will break this down into sections;

  • Within the same worksheet
  • Between different worksheets
  • Between different workbooks
  • Using the clipboard
  • Using paste special

Copy/Paste in the same worksheet

Basic sample data

Basic sample data

We have some data in cells A1:A11 and want to copy it across to another part of the same worksheet. For this example, let’s say I want to start pasting the data starting from cell E12.

Let’s just think about how we would do this manually first – highlight the cells A1:A11, right click, select COPY or press Ctrl + C, click into cell E12 and press Ctrl + V or right click and select PASTE. I don’t need to select a range of cells to paste into, just pick the top left hand cell to start pasting from.

It’s exactly the same in VBA:

Say what you want to copy, what you want to do to it i.e. copy and tell it where to start pasting from.

Range(“A1:A11”).Copy Range(”E12”)

Note, I do not need to use the word PASTE anywhere in my code. By keeping all the code on ONE line, the cell reference that follows the space after COPY is the DESTINATION cell to start the paste operation from.

The key to keeping this simple is to ensure the copy/paste instructions are kept on the same line. If you move to a second line of code, the data is stored in the CLIPBOARD and has to be extracted again. I’ll show this a bit later but most of the time it is unnecessary.

If you want to CUT and PASTE then replace the word COPY with CUT – simple as that!

This code assumes that we are copying and pasting within the ActiveSheet. If we want to be a little more precise to avoid any errors then we can specify the name of the worksheet we want to copy and paste in;

Worksheets(1).Range(“A1:A11”).Copy Range(“E12”)

By being that little more specific we don’t have to worry too much about which worksheet we are in when the code runs. We can be even more specific by naming the workbook too and that avoids any issues of current workbook or current worksheet.

Workbooks(“Demo”).Worksheets(1).Range(“A1:A11”).Copy _ Range(“E12”)

In keeping with writing code I have used a space followed by an underscore as a continuation marker. This tells the VB editor that this is a continuous line of code and not two separate lines. This is useful when you write code as it means you do not have to keep on scrolling across to the right to read a long line of code…particularly handy when writing long lines of code for copy/paste!

Copy/Paste between different worksheets

Basically you need to reproduce what we did in the example above only now you have to specify a source sheet and destination sheet. Think of it a bit like an equation that needs to be balanced either side of your copy/cut command;

Worksheets(1).Range(“A1:A11”).Copy Worksheets(2).Range(“A1”)

As in the previous example I can be a little more precise by naming the workbook at the beginning just in case I don’t happen to be in the workbook when the code is running.

Copy/Paste between different workbooks

Same again, but this time I need to make sure I reference the source workbook name and the destination workbook name as well as the sheet and range references;

Workbooks(“Source”).Worksheets(1).Range(“A1:A11”).Copy _

Workbooks(“Destination”).Worksheets(2).Range(“A1)

Remember to keep the code on one continuous line or use the line continuation marker ( _).

When using the CUT/COPY code, because you can be very precise with the workbook, worksheet and range references, as long as those workbooks are open you don’t have to make either one of the workbooks active. The code will run irrespective of which workbook you are in, making this code very efficient as you don’t have to activate or select anything for it to work.

Move/Copy/Paste entire worksheets

Rather than just CUT/COPY a range of cells, you may want to do it to an entire sheet.

Virtually no difference with the code above, only now we don’t need to reference the cells, only the worksheet.

Worksheets(1).Move Worksheets(3)

Worksheets(1).Move ,Worksheets(3)

The first line will move sheet 1 to the LEFT of worksheet 3 and the second line of code, the addition of the COMMA moves the sheet to the RIGHT of worksheet 3. You could of course COPY the sheet instead…works exactly the same way.

The make the code a little clearer you can use the word BEFORE:= or AFTER:= rather than no comma, or comma respectively to place the worksheet.

Similar to copying cells between workbooks, you have to specify the source and destination workbooks.

Workbooks(“Source”).Worksheets(1).Move _ Workbooks(“Destination”).Worksheets(1)

The absence of a comma after MOVE will place the worksheet to the LEFT of sheet 1 in the destination workbook, and a comma will move it to the RIGHT.

Note there is no reference made to cells. When you copy or move an entire sheet you cannot paste it into a cell or range of cells unless you copy all the cells in a sheet rather than the sheet itself. It’s a bit like tearing a sheet out of a book…you don’t stick it on top of a sheet covering what is already there, you slide it in before or after an existing sheet.

Copying to the clipboard

There may be times when you don’t want to CUT/COPY/PASTE immediately to a known destination as you may need to do some work first in a sheet and paste some information below it.

Range(“A1:A11”).Copy

…do whatever else needs doing with your code

ActiveSheet.Paste

Application.CutCopyMode = False

Unlike the earlier examples the COPY and PASTE actions are now two separate lines of code. When you COPY it is put on the clipboard. Eventually you will be in an ActiveCell and that is where you use the ActiveSheet.Paste command. The final line above is used to clear the clipboard.

Note that this method only allows you to copy one thing at a time to the clipboard. There are ways of working with multiple items but it’s beyond the scope of this blog.

Using Paste Special

If you are used to Excel 2010 or 2013 you will know how easy it is now to access the features of Paste Special. To write this in VBA is not that difficult.

Pick list showing options for Paste Special

Pick list showing options for Paste Special

Copy your cells exactly the same as before, go to another cell, same sheet/same workbook/other workbook and use the .PasteSpecial command followed by a bracket. When you type the bracket you will see the prompt to show all the parameters and the first one is the paste TYPE. Choose from:

  • xlPasteAll
  • xlPasteAllExceptBorders
  • xlPasteAllMergingConditionalFormats
  • xlPasteAllUsingSourceTheme
  • xlPasteColumnWidths
  • xlPasteComments
  • xlPasteFormats
  • xlPasteFormulas
  • xlPasteFormulasAndNumberFormats
  • xlPasteValidation
  • xlPasteValues
  • xlPasteValuesAndNumberFormats

You then have two more optional parameters you can set;

Special operation:

  • xlPasteSpecialOperationAdd
  • xlPasteSpecialOperationDivide
  • xlPasteSpecialOperationMultiply
  • xlPasteSpecialOperationNone
  • xlPasteSpecialOperationSubtract

And finally, decide whether you want to SKIP BLANKS or not. Set this parameter to TRUE or FALSE.

You can use the CutCopyMode line of code again to clear the clipboard and remove the dashed lines (2013) or the marching ants around the copied cells.

The one option that is missing from here is PASTE LINK.

Follow the code to do COPY and PASTE using the clipboard but on the PASTE line of code add a comma and TRUE. This will then paste in as a link.

ActiveSheet.Paste, True

Note that you will not be prompted with anything when you enter the comma…you just have to know.

So there you have CUT/COPY/MOVE/PASTE/PASTESPECIAL/PASTE LINK in VBA for Excel. No more manual cut/copy/paste on those monthly reports!

Advertisements

Posted on March 18, 2014, in VBA and tagged , , , , . Bookmark the permalink. 4 Comments.

  1. Thanks Richard for this thorough article. One of the first thing I forget when developing with XL VBA is Copy / Cut & Paste. Don’t know why. Maybe because there are dozen of various techniques depending on the situation. Will keep your article close!!!

  2. Thanks Richard, Kudos to such a well thought out post. I agree with Daniel when he says “there are dozen various techniques depending on the situation.” I have another one to add to the situation and would like your take on it.

    In the case where you are on a sheet, in a particular cell where you may need to paste data in the future.

    You go to another sheet and perform some code, and then you want to go back to that original cell to paste some result. Is there a clean way of doing this? I find once I leave the sheet, with a particular cell of focus and then try to come back to that cell of focus is where I start turning code into unreadable-Spaghetti, lol Thanks for any feedback.

    • My immediate thought would be to create a variable to store the address of the cell you “might” need to paste to later. Like that you can build an IF statement later on so that if your condition is met (or not) depending on what you are aiming for, and if true, copy the value to the cell stored earlier in the routine. Referencing should be made easier by storing the relevant information in one or more variables (cell address. cell value etc.)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: