How to Write VBA Macros to Copy and Paste Cells in Excel - Part 1 of 3

The Opinion Lighthouse
32 followers
13 Video Views·Nov 8, 2022
How to Write VBA Macros to Copy and Paste Cells in Excel - Part 1 of 3
Welcome to Excel Campus!
My name is John, and this video is part of a three-part series on how to copy and paste cells with VBA macros.
This video is from my VBA Pro course, where you will learn how to automate Excel to save time with your job and advance in your career.
If you're interested in learning more about VBA, I recommend checking out my free training series on macros and VBA.
I will put a link at the end of this video where you can get access to the free training series.
Now let's learn something about copy and paste.
In the next three videos, we're going to talk about three different methods for copying and pasting in VBA.
This is a very common task that we do in Excel, and it's good to know how to do it in VBA as well and the different methods you can use.
You can make it a little more efficient than a normal copy-paste in Excel.
The first one we'll talk about is this range.copy method.
Then I'm also going to talk about how to set the values of cells to the values of other cells, and then we'll look at some paste special examples.
Let's just start with the range.copy method.
The range.copy method is very straightforward.
It's basically you just use this copy method, and then you specify the range where you want to paste to.
I'm going to go ahead and type one out here so you can see exactly how it works.
We're specifying range A1, and then I hit .copy.
Once I hit the space, you can see that there's just one optional parameter here for the destination, and that would be the range that we want to paste to.
In this case, if we want to paste to cell C1, then basically this is just going to do a straight copy and paste from A1 to C1.
Let's go ahead and start stepping through this so we can see how it works.
I'm just going to delete this line here because I have the same line below, and I'm going to move the VB Editor over just a bit here.
I basically have two workbooks here.
Book 1 here has a few items in range A1 to A3, and then Book 2 is blank.
Since I didn't specify the active workbook or the active worksheet here, it's best when I run this to make sure that Book 1 is active because I want to start here with the active workbook.
That's my active workbook.
I'm just going to hit F8 to start stepping through this.
You can see when I ran that line of code, A1 was just copied directly to C1 there.
It's just a normal copy-paste.
It's not going to do any paste special like pasting values or anything like that.
It's just a straight copy-paste.
You can also use the copy method on a range of cells.
In this example here, I've specified range A1 to A3, and then I'm going to paste to this destination D1 to D3.
I'll hit F8, and you can see everything here basically has been copied right here within my worksheet.
You also notice that I'm not having to select cells here before I do this copy and paste.
That's one thing you don't get in the macro recorder, or what you do get in the macro recorder is all the cells that you select.
You don't have to do that with VBA.
You don't actually have to select those cells before you do a copy or a paste.
You can also use range.copy to copy between worksheets.
Here in this example, I'll just move this back over so you can see it a little bit.
Basically, we're copying from sheet 1, range A1, and then our destination here will be sheet 2, range A1.
You can use this copy method between worksheets.
Basically, if I run that now, this is going to again start here, just copy A1, and then paste it to sheet 2.
If I go to sheet 2, you can see here's my paste right here.
This also works between workbooks as well.
Here, I basically have more of a fully qualified line of code where I'm starting with book 1, sheet 1, range A1, or cell A1,
and then copying it to book 2, sheet 1, A1.
You'll also notice I broke this into two lines.
I did that with this underscore character here.
If you end the line with an underscore character, you can then continue it on basically to the next line.
Space, underscore character, and then you can continue that code on to the next line.
I usually also indent it here just so I know that that line's continued on.
It's a little easier to read that way.
I'll go ahead and run this line of code.
You can see now that basically I took what was in sheet 1 here, A1, and then pasted it to the second book down here.
This is book 2 in sheet 1, cell A1.
That's a way you can use range.copy to paste between workbooks, worksheets, or just ranges within your workbook.
That video was from my VBA Pro course.
If you enjoyed it, click here to get access to my free training series on macros and VBA.
You will also receive information on the course.
Thanks again for watching, and I'll see you soon.
