VBA Macro to Copy Data from Another Workbook in Excel
Vložit
- čas přidán 4. 07. 2024
- Sign up for our Excel webinar, times added weekly: www.excelcampus.com/blueprint...
Learn how to use VBA macros in Excel to copy data from one workbook to another, and how to copy data below an existing range.
Download the Excel file to follow along: www.excelcampus.com/vba/copy-...
In this video I explain different techniques in VBA to copy a range of cells from one workbook to another. You will learn the regular Range.Copy method, and I also explain how to copy paste values only.
I also explain how to do a dynamic copy & paste, where the copy range changes every week. You might be exporting data and not sure how many rows you need to copy. I explain how to use VBA to find the last used row and reference the dynamic range.
I also explain how to copy data below the last used row in the destination sheet. This allows for a fully automated copy and paste to add or append data to an existing data table or range.
Additional resources mentioned in the video:
Video series on 3 ways to Copy/Paste with VBA: • How to Write VBA Macro...
Video series on 3 ways to Find the Last Used Row/Column with VBA: • Find the Last Row, Col...
Video series on The Personal Macro Workbook:
• The Personal Macro Wor...
VBA Context Assumptions (how to reference workbooks and sheets): • 2 Critical Excel VBA A...
🚀If you're new to VBA then checkout my free upcoming webinar on The 7 Steps to Getting Started with Macros & VBA.
👉Register here: www.excelcampus.com/yt-vba-we...
00:00 Introduction
00:11 How to Use VBA Macros to Copy Data to Another Workbook
00:50 Copy Data from One Workbook to Another Using Excel Macros
01:09 Important Points to Remember
08:26 Step though Code
Here's the link where you can download the files I use in the video. Download the Excel file to follow along: www.excelcampus.com/vba/copy-paste-another-workbook/
If you're new to VBA then checkout my free upcoming webinar on The 7 Steps to Getting Started with Macros & VBA. Register here: www.excelcampus.com/yt-vba-webinar
Lots of additional links and resources in the description above. Please leave a comment below with any questions or suggestions. Thanks!
I think you just saved my internship!!
word bro LOL
same hah
Thank you very much for this high quality video. You are very detailed and clear in your explanations which helps tremendously. I've been manually copying and pasting data from 12 spreadsheets to a master spreadsheet monthly and am looking forward to automating it.
Thank you Jon! You've just helped me to become a successful man!
WOW! This is exactly what I needed. It seems like you made this tutorial video for me.
I just modified it to go through 12 Sheets (January through December) using a For/Next Loop to copy non-blank range then paste values in the next blank row after destination file/sheet is cleared once.
Thank you, thank you, thank you.
Hi Jon.. great video on various methods of copying and pasting between workbooks. Your sample code and presentation are clear and help me organize my thoughts and the steps to proceed to the solution. Thanks also for providing the sample workbooks to follow along.. very helpful. Thumbs up!
I'm working on a project and cam across this video, what you've done will help so many! Thanks so much Jon awesome video Great job Sir!
This tutorial shows exactly what I'm trying to achieve with an Excel project I've started recently...Brilliant ! ;-)
1a) 3:13 - Open/ Close source Workbook
2) 10:02 - Clear Existing Data in destination before Pasting
3a) 7:15 - Copy/Paste below Last Cell in destination Workbook.
3b) 10:36 - Alternate destination Workbook reference.
3c) 11:36 - Copy/Paste below Last Cell in destination Workbook if worksheet name is unknown.
So comprehensive, so successful
Learn VBA with Jon
Thank you bro, thanks a lot!!!!!!
I am so happy with the content of this tutorial, thank you very much Excel Campus !
Just found this today and it helped me a lot to try and automate manual copy and pastes of my teammates. Will be trying to put some of these in a button.
Dude, this lesson is soooo awesome, it's exactly what I needed ! thanks a lot, and there we go a new Like and a new subscriber ;)
Thanks to Excel Campus!
Thank you for the very detailed and precise video and code. I managed to solve my problem.
Thanks Jon for the helpful tips. Putting it to work right away :)
Awesome! Thanks Rachad!
Very useful & I've incorporated this into my latest video where I've created an awesome tool for the average side-hustler. Thank you!!!
Thank you. You make my life easy to live.
This helped me so much at work. Thank you!
hmm... DUde, what to say this was actually some instructive information! Thank you for explaining the basics!!!!! ❤👍
Great job, saved me a lot of time! God bless you
Thanks Jon. Very comprehensive
This so great. thanks a lot for sharing this videos!
Thank you, this is fantastic help.
Thank you, Jon, for your video. I am starting a new project at work, and this video showed what I needed for part of it. My question is, how can I take a look at your webinar? The portal provided in the link doesn't offer any date or time choices for me to use to sign up to watch the video.
Very helpful, thank you!
Brilliant ! Thank you so much
Excellent contents, Thank you!
Thanks Jon :-) Really Helpful
So excited to use this!! :)
thank you very much as a newbie at VBA it captured my mind and I got passionate about thank you for your videos
Happy to hear that! 😀
Thanks for sharing a good and fast way.
it's fantastic dude
thanks you, thanks a lot
greeting from Istanbul
Hi! That is a great video, thank you so much for making it! In my case the name of the workbook I copy the data from changes, is there a way to deal with that?
Did u get it
Thank you so much. This is very helpful
Hi Jon, thank you for sharing this video.
I have a question, if we want to use same macro for 2 different workbooks having different values, different number of rows but, there is one common value say in B1 (Items) for both the workbooks. Say, I use filter to sort out Items in B1 and copy the data of different items in separate new excel sheets. Is it possible?
Very useful. Thank you!
Great video and easy to understand 🙏🙏
Thank you for your useful tutorials! I quickly earned "Wizard" status at my new job by writing a macro that copies in data from date stamped system generated files using Application.FileDialog to set workbook variable and your lastrow/range.copy videos.
Thanks A.J.! And congrats on wizard status. I love it! Sounds like you are able to help a lot of coworkers save time as well. Awesome! Have a good one. :-)
I am trying to figure out how to do that. Any references to point me to?
@@ClintSevilla Hello! Honestly since doing this I’ve learned to use the Power Query for data import. That would be worth your time learning.
I’m think for this one I used the “Open file” dialogue box and set a range in the destination equal to the raw data.
Wiseowl VBA tutorials were a great job off point.
THANK YOU VERY VERY MUCH FOR YOUR PERFECT HELP
Thank you John for a well paced video with all the steps described and covered. I ended up here while searching on how to "SET" an open workbook's (file) name to a workbook variable and later copy some contents to another open file. Watching this video, I think I can use "Set MyWorkBookVariable = Workbooks(FileNameVariable)". I will test/check this later. In any case, I got more than I asked for. You have pretty much all of the things in this video which I was trying and thinking to implement in the current project I am working. So thanks a lot for great ideas and very helpful video. I will be watching your another video regarding Personal Macro Workbook. Your efforts and help is very appreciated.
Thanks a lot. You made it look so easy.
Glad it helped 😀
Thank you for your good work
Hi Jon,
The video is really nice!
You will appreciate the fact that I could able to select a dynamic range and export it to another worksheet at the first available row.
The only issue:
While using xlUp (I have used it 8 times in four different macros for different ranges) and the file size has gone up drastically to 129 MB. Can you help me with tips to reduce the file size? Can I use xlDown? Please help
simply excellent !!!
Thanks Jon. Very useful. How about copying the data and pasting it in the other worksheet depending on the date today
Hi, this is very helpful. thank you. Just one question, when pasting the data where would you include paste special to just paste values.
thanks
hello, thank you for the help!
just a question if the data being copied has a cell format the copy paste of last row is copying also the cells that are formatted and add the new data below empty cell with values and the formatted empty cell. how do just get rid of those empty cells without values but was formatted.?
Hi Jon, is there any link to make an invoice and just specific cells to be saved on a data sheet into the same workbook every time I open and close the document?
Thank you, Jon!
How I can watch the recorded webinar? On register page I can`t choose any date and why I can`t register.
Thank you for an excellent video, im interested in creating a similar code that would pull just the unique records from a large excel file of 100k rows. Can it be done without crashing?
Thanks for this video, it is very helpful. By any chance do you have any video explaining same topic but what Im looking for is to copy a value by looking referencing to dynamic data, such as dates... if you are on a certain week then copy certain row, then if the week changes copy and paste another row and so on... so ranges will change but date will bw the main input...
Thank you very much for this insightful video. However, I would like to ask if we can create a macro formula under paste active workbook using the reference that is displayed in a specific excel cell. By using this, we can easily change the path or reference by editing it in the cell instead of updating the macro.
Hi John, thank you for this simplified video, it is very helpful.I can certainly use this code for tasks where I know the name of the source range, however my main issue is; I use an application that generates time sheets. The issue is, this filename and sheet name changes every time. Is there a way to reference this workbook/worksheet.I will already have open my destination file ("current tsheets", and my "PERSONAL" file, so how could I reference the source file.Could really use your help on this one.Jo
Thank you for your video and explanation.
But when we select folder and open workbooks with different name with macro, that is, workbook name and also rows are variable, what macro code should we use?
Thanks in advance...
Hi John, Required to create multiple files/workbook with the help of 2 master data. Example in Master data file, created 2 seprate files with State and company wise seprate excel sheet/tab. With help of VBA need to create seprate files created in one specific folder with Company wise (also it should have one more tabe with state wise).
Great tutorial
Hi Jon, thank you for all your excellent video Tutorial,
I have small query if you could help, I have lots of excel file sent to me with different extensions (.xls,xlsm,xlsx) which I enter them in main workbook, is there a way to enter the 3 different extensions in the macros?
Your reply would be much appreciated. Regards Don
Hi, Thank you for the video, was very helpful. Just one comment/question. I guess there should be no filters/all filters cleared in the the source file/sheet where the info is copied from. otherwise the code copies only filtered data.
Hey John, Very useful video. Just have 1 question, if the source data is keep on updating and we need the new data in destination file that is getting updated every day, then how can we do do that with the help of macro? Please guide. (Usually I do a vlookup to get the newly added lines)
Thank you so much
Thanks! This is helpful. May I know if you have tutorial that will show how to copy only filtered data? Not all data
Thank you Mister
Thanks for the video it helped with my project.
I am stuck and here is how.
I have a workbook with 5 sheets, named for each day of the week.
I want to be able to have say Tuesday opened and copy it to another workbook. However tomorrow I will have Wednesday opened and will need to copy that as well.
Q, is there a way to do this?
Oh I forgot, all the sheets need to go onto the same sheet in the other workbook. Basically a data file that will grow day by day on the same sheet.
I thought about just repeating the same code 5 times then thought there has to be an easier way I dont know about yet
Thank you for share
Brilliant thankyou
Jon I just stumbled onto this video after spending over 12+ hours of watching multiple CZcams authors show how to import a range of data from multiple workbooks into a "master file" workbook. I am an extreme beginner trying to build a new system to be used at work, but after accomplishing everything else I needed, this (import/copy) is the wall I'm struggling to get over. Does your method allow for the copying of multiple workbooks using the copy/paste below last cell method? Any assistance (or direction pointing) would be greatly appreciated. Thank you for your time and hard work.
Hello Jon
This is very useful video and the sample spreadsheet provided by you. Thanks a lot.
I have two questions, need your help with.
1. How can I copy data from one workbook to another workbook. Additionally the data should be pasted to worksheet which do not have any data in it, however other may have some data?
2. How can I copy paste data from one workbook to another workbook, where anther workbook and its workbook has different name everytime, I want to paste data?
Looking forwards for your assistance.
Thanks
Hey Jon - would you have the knowledge on how to pull data from multiple xlsm's that have merged cells and dropdowns into a master workbook using vba?
Really, very good.
Thank you!, :)
Great Video! How would you copy and paste above an existing range?
Excellent
Hi Jon and everyone. towards the end of the video 12:30 you stated that you could change the worksheet from the worksheet name to the number. is there any way that you can do that for a workbook that has a different name each time?Basically we are moving all of our old information from the old rater to our new raters and I'm hoping there is a way I can create a macro that will be able to transfer the information form any requested(open) workbook to our new one.
Is it possible to have a prompt appear to allow end user select the file from a file browser to copy from so it is not hardcoded in the macro like if they might be copying from a different file each time?
Thanks for the video, just want to ask how about a vba for different workbooks name?
Hello, very helpful video. I get data in raw format I have to prepare that raw data in a particular format. How to copy data in row A and get the same pasted in row B using VBA.
Hi Jon.Thank you for this video. It saved me a lot of work.. How can I copy the data from the other sheet while keeping the same format?
i have some images in my sheet which are not getting copied and also the text box text size gets bigger when i copy it to the current sheet
Do you have any tutorials that show this, but using a filepath that the workbook "report" changes week over week? So for example, I want to run a macro that imports data into a specific workbook, but the data source file is updated on a weekly basis and is saved with a different name: source_data_05_23, source_data_05_30 etc
Thank you... I have a question. I am trying to copy data from one workbook to another through opendialog method. Does anyone has any direction on how to do this?
Thanks for the nice video. This is useful. May I check how to copy only new rows [there maybe more than 1 new row] from source excel, if only those rows are not found in the destination excel file and matches criteria [eg. West region]?
Thank you
Merry Christmas and happy New year
Jon, you’re my new best friend!
I meant to also say thank you… so THANK YOU!!! You seriously saved me about 3 days worth of work! Between your help and the KuTools add-on that I just got, I’m on fire now! Lol
Can you add a video about the DDE commands (major ones) with the explination
Cerci Lanister same question
Hi Jon, I have some basic VBA macro, i wonder if you have any video on copy various data into 1 summary workbook ? if there is, please let me know your VBA link. Thanks.
Thanks you so much! What is the code to copy and paste from multiple workbooks??
Hi
I have a question, In case if any of the user try to change the filename, sheetname, path etc. Then we receive a run time error. I wan't to know what is the way to prevent run time error incase filename or sheet name or pathname is changed ?
I think we use a codename in vba editor incase of sheet name is changed to prevent run time error. But, when you change filename or path what is the way to prevent ?
Hi Jon. I have a task in which i want to take input from user the column digit and then i have to copy that column cell range to a new file which i will save as .xlsx on shared drive. Could you please guide me through how will i achieve this?
Perfect brother
Thanks, @TheGamer-we2br ✌️
Greetings
may i get your help
i wanna make 2 sheets
1st sheet have 300 items (300 row) and 10 column.
2nd sheet have 300 items also but each items have 15 revision(4500 row) , and same 10 column in the first sheet.
what i want is that the first sheet have last status for each items, and when i select the revision from (column 3) and when i record in the first sheet it's copy Automatically to the 2nd sheet in the row which have the same revision and the same items number(column 1).
Thanks
Thanks, just what I wanted. Just a small question, the source file from where I copy keeps on changing, is there a way to make that dynamic, so whenever it changes, the same changes in the code? I am using getopenfilename to get latest file name, but I can't figure out how to incorporate the same in the macro.
Instead of pasting below the last row cell, what should I change for pasting right next to the last column cell?
Hi Jon, how about copy and paste a workbook with a VBA. Will it work? I ask you this questions because I noticed there are duplicates Alt + F11 at the right in the VBAProject properties.
HI sir,
How can I move data from one sheet to another using filter in Macro??
Data need to move with multi-parameter or as per criteria in different sheets.
Please help sir
Informative: i have one problem that the excel in which i am trying to copy the data are on SVN Repo, do we have support in VBA to do SVN operations live Lock the sheet and then open it and copy the data and save it. I was going through Bash script to achieve it , will VBA has support to SVN, Please guide on this. :)
What if I just want to transfer 1 selected row to another workbook?
Dear sir...is there any code that works in both the scenario..in same time.. whether the file is open or closed..it's gets the data..
amazing
My son is using a macro he has recorded rather than written to transfer one record displayed vertically from a worksheet to another worksheet but displayed horizontally, e.g. compiling a list one at a time using the macro assigned to a button. The problem is, each record is becoming over written each time the macro is run. It's GCSE ICT so wont require coding, is there something we should be doing as part of the recording ? Any help would be appreciated
Great video.
Is it possible to send the selected information to a defined sheet in the recieving workbook?
For example, my original data was collected on a Monday (I can have a cell value equal to the day, as a reference), and I want to copy it to the "Monday" sheet in the new workbook. Then i will collect new data on the Tuesday, which i want to send to the "Tuesday" sheet.
Ultimately, the new data will have a reference, this will be transferred to the new workbook in the sheet labelled with the same reference.