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
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.
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;
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;
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;
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.
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.
…do whatever else needs doing with your code
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.
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:
You then have two more optional parameters you can set;
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.
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!