VBA Macro to Copy Data from Another Workbook in Excel

The Opinion Lighthouse
32 followers
43 Video Views·Nov 8, 2022
VBA Macro to Copy Data from Another Workbook in Excel
Welcome to Excel Campus. My name is John and in this video I'm going to explain how to use VBA
macros to copy and paste data to another workbook. So we're first going to look at a very simple
example of copying this static range to another workbook, but of course that's not always what
we need in the real world. So we're also going to look at examples of copying data where the number
of rows in our source range changes and also where the destination range already has existing data.
We might want to paste below that or we might want to first delete this existing data and then paste
to this range. So we'll first start with the basics of doing a simple copy and paste between
workbooks. I'll make the VB Editor full size here so we can dive into this code and I will also make
this workbook available for free download and I'll put a link in the description below this video so
you can follow along. So copying data between workbooks is actually very simple and only
requires one line of code and we're going to do that using the range.copy method and that's
this line here. Now you see it's actually two lines but I've really just continued on this one
single line with the underscore character. So really we're using this range.copy method and
I'll just add a space here and we can type it out. Type range.copy. If we tab into that and hit space
we'll see that the copy method here has one parameter which is the destination. So this is
the destination range of where we want to paste to and that's exactly what this line of code is
doing up here. So we have the source range. We're also referencing the workbook and the worksheet
where with this range exists. So this exists in this new data workbook. The workbook name is
newdata.xlsx and then this sheet that exists in is the export sheet and then we have this range
A2 to D9 that we're going to copy and then the destination for that copy or where we're going to
paste to is this range right here just starting in cell A2 on the data sheet in the reports.xlsxm
workbook. So let's go ahead and test this out. I'm first going to delete this line of code here
ctrl y is the keyboard shortcut to do that. Then I'll move the VB editor back over here. I have all
of these files kind of lined up so we can actually see the code run and we'll go ahead and just place
the text cursor in here. You can hit F8 on the keyboard to step through this or F5 to run it.
So again this is just one single line of code here. You can see the code highlighter highlights
both lines. Hit F8 on the keyboard and then we can see over here that this range has been pasted
right here in our destination workbook which is reports.xlsxm. We'll go ahead and hit F8 again to
stop running the macro and then there are a few important points to remember here that I want to
point out. So I've listed these right here but it's important to understand that first of all both of
the workbooks must be open on the computer and we can use code to open and close the workbooks. So
we can use code to first open the workbook then run this copy line on it and then even close the
workbook again if we don't need it open. I've included in this workbook this open close methods
module that shows and has some macros for both the workbooks.open method and the workbooks.close
method. So I put links to the help pages for those as well and you can check that out. But if you do
want to first open the workbook maybe you're exporting data you want to open that workbook
then copy and paste and then close it. You can absolutely do all of that with macros. And then
we'll jump back here. Another important point is that we must reference the correct file extension
in the workbooks property. So you'll notice here that I'm referencing the file extension as well
of each of these files. .xlsx is our new data file the reports file is a macro enabled workbook
.xlsm but it's just important to remember to reference these file extensions along with the
correct file name for the workbooks property. And another important point is that these workbooks
do not have to be macro enabled. I listed that right here. They can both be .xlsx files so you
can copy and paste between files that are not macro enabled. So you could store the code somewhere
else. You could store it in your personal macro workbook if you're doing this task frequently.
I have a whole other video series that explains the personal macro workbook in more detail
and I'll put a link to that in the description below this video. But basically there you could
store a macro in that workbook and run it anytime because that workbook opens every time you open
Excel. And then another important point is that we do not need to select or activate the workbooks
or the worksheets or even the ranges that we're copying and pasting to. So if you've used the macro
recorder before and got code from the macro recorder to do this task you might have a lot more
code than this because the macro recorder is going to record your steps to activate or select the
other workbook, select the worksheet that you want to paste to and copy from and all that. You're going
to have additional lines of code for that. But as long as we create the correct references and context
to the workbook, worksheet, and range that we want to copy from and also paste to, we can do this in
all in one line of code without having to select those other objects. If you're enjoying this video
please click that big red subscribe button below the video to subscribe to our channel
and also click the notification bell icon there to get notified when new videos are published.
Now there are other ways to copy data and I've included a few additional macros in this module
here. This one down here uses the paste special method. So this is using two lines of code. The
first line of code is going to use the copy method and just copy that range to the clipboard
and then we're going to use a separate line of code with the paste special method and this allows us
to choose the paste type. I currently have it set to paste values but we could also paste formulas
or formats or any of the paste special options. And I have a whole other video series that explains
these copy and paste methods in more details. I'll put a link to that in the description below this
video as well. And then finally we could also use the method to set values and I have a macro for
that here as well. And in this case here we're just setting the value of this range to the value
of another range which would be our copy range. So this is an alternate technique for copy and
paste values. But again the main point here is that we can do this operation very easily with
one or two lines of code as long as we reference the workbook and the worksheet and the range
properly. Okay so now let's take a look at a bit more of a real world or advanced example where
the size of the ranges might be changing. So you might be doing this task every week or even every
day and the size of the data ranges changes for both the copy range and maybe you want to paste
below the existing data on the destination range. So I've included a macro in this workbook as well
and it's here in this paste below module and it's called copy paste below last cell. Now I have a
few other videos that explain how to find the last used cell on a worksheet and also how to do this
entire process of copy and paste below an existing range and I'll put links to those in the description
below this video. So I won't go into this in too much detail but we will walk through this macro
and see how it works. So the first thing we have up here is just a few variables that we've set
for those worksheets for our copy range and worksheet and workbook within the copy range
or where we're going to copy the data from and then the destination workbook as well. And then
we're going to use the range dot end method here to find the last used row in column A of both of
these sheets. So this one here will set the variable for the last used row in the copy range
and then the same code is used down here for the destination range as well. And then we just have a
line of code that does the copy and paste. So let's take a look at this. We'll step through it and see
how it works. I'm going to move the VBO editor over here a bit. Go ahead and hit F8 on the keyboard
to start stepping through this. We can see those two variables have been set. We'll run that
line of code. Again that's going to find the last used row on the copy sheet. We can see that's
returning a 10. This line here, I'll hit F8 to step through that one, will show us the last used row
on the destination sheet. And actually we've offset that by one. I'll move this over here so you can
see at the end we've offset that by one. So we've added an additional row there and so that's really
setting our destination row, our last row here, to the first blank row. And then this last row down
here is again using the range.copy method, that single line there. And here we're just using our
variables within the range references. So we've joined or concatenated this variable here that's
returning a 10 to this range reference. So this is actually going to return a 2 to d 10. And then
we're going to paste that starting in column A and cell, I'm sorry, row 12. So this would be return
a 12. So we're just going to do that copy and paste there. I'll hit F8 on the keyboard to run
that line. And we can see our new data has been pasted right down here. And again this is fully
dynamic. So when we come back and run this next time, maybe we have less rows here in our copy
range. I'll just delete some rows just so we can see how this works. And we'll still have this same
number of rows in our destination range. And just jump in here, run the macro. And that again is
going to paste all of those new rows down here to the bottom of our destination range. And then
I've included another macro here below this one, which will clear the existing data before doing
the paste. So the setup is really the same here. Let me move this over so you can see it. Really
the same code. The only change is we're going to clear the contents of that range after we find the
last used row in our destination range. We're going to use the clear contents method to clear
out those cells. And then we do the copy and paste. So again we could just move this over here. We'll
just run this real quick to see how it works. Then again we'll delete everything that was existing
in that range there. Oops there we go. And then we'll see we just have a straight copy and paste
of the new data into this range. And then one final thing I wanted to mention is that we can
use alternative references for the workbooks property. So in this case here I've used the
this workbook property instead. And this workbook will reference the workbook that the code is in.
So the code is currently in the reports workbook. So instead of this line of code here or this
section, I've replaced it with this workbook. And the advantage to using the this workbook property
is that if the file name changes, if we were to change the name of the reports workbook to
something else, we won't have to go change our code. It'll just always reference this workbook
because this is the workbook again that this code module is stored in. You don't have to do this. As
I mentioned before you can copy and paste from workbooks and the code can be stored in a completely
separate workbook or your personal macro workbook or an add-in or something like that. But I just
wanted to point out that you can use this reference as well to again make your code a little more
dynamic and require less maintenance. And then one other issue you might face is that you don't
know the name of the worksheet from the data you're copying from. So if you're exporting data
from a system you might not always know the worksheet name. If we jump back over to Excel here
this tab name might change or the sheet name might change every time you export new data. And we can
also account for that in VBA. So if we jump back over to the VBA editor here in this line of code
where we set reference to this worksheet and created a variable for it, instead of referencing
the worksheet by its name we can instead reference it by its index number or the position that it's
in within the workbook. So that would be the first sheet in the workbook. We just reference it with
the number one there, no quotation marks. And again that's going to then just reference this
first worksheet here in the workbook regardless of what the name of it is. And of course if the
exported workbook has multiple worksheets and it's always the second sheet that you're copying
from or something like that, then you can change this reference to a two or whatever the position
is of the sheet within the workbook. So I hope that helps you save time with copying and pasting
data between workbooks. If some of those techniques are new to you I put quite a few links in the
description below this video with additional resources and training. If you're new to VBA I
highly recommend taking the time to write out this code and practice. That will really help you
learn VBA and also go beyond the macro recorder. So if you have any questions please leave a comment
below. If you'd like to learn more about macros then join me for a free upcoming webinar on the
seven steps to getting started with macros in VBA. During this training I'm going to explain
how to write your first macro, what order the code runs in, what the dots between the words mean,
and some of these complex things that are hard to understand when we're starting out with macros.
So this will really help you automate Excel and save a ton of time with your job. So click the
link right below this video to get registered for the free webinar and I look forward to seeing you there.
