VBA Macro to Copy Data from Another Workbook in Excel
Вставка
- Опубліковано 24 лип 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!
Thank you Jon! You've just helped me to become a successful man!
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.
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.
I think you just saved my internship!!
word bro LOL
same hah
same hah
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 ! ;-)
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 !
Thank you for the very detailed and precise video and code. I managed to solve my problem.
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.
Thanks to Excel Campus!
Thank you. You make my life easy to live.
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 ;)
This so great. thanks a lot for sharing this videos!
Thank you, this is fantastic help.
This helped me so much at work. Thank you!
Thanks Jon. Very comprehensive
Great job, saved me a lot of time! God bless you
Thanks Jon for the helpful tips. Putting it to work right away :)
Awesome! Thanks Rachad!
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.
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.
Brilliant ! Thank you so much
Very helpful, thank you!
Excellent contents, Thank you!
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 & I've incorporated this into my latest video where I've created an awesome tool for the average side-hustler. Thank you!!!
Thanks Jon :-) Really Helpful
So excited to use this!! :)
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
Thanks for sharing a good and fast way.
Very useful. Thank you!
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?
simply excellent !!!
THANK YOU VERY VERY MUCH FOR YOUR PERFECT HELP
Thank you so much. This is very helpful
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.
hmm... DUde, what to say this was actually some instructive information! Thank you for explaining the basics!!!!! ❤👍
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?
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 good work
Great video and easy to understand 🙏🙏
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
it's fantastic dude
thanks you, thanks a lot
greeting from Istanbul
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, 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.
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 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.
Thanks Jon. Very useful. How about copying the data and pasting it in the other worksheet depending on the date today
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...
Great tutorial
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.
Thank you so much
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
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
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).
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.
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
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! 😀
Brilliant thankyou
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)
Really, very good.
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
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).
Thank you Mister
Thank you!, :)
Thanks! This is helpful. May I know if you have tutorial that will show how to copy only filtered data? Not all data
Thanks a lot. You made it look so easy.
Glad it helped 😀
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.
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?
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?
Can you add a video about the DDE commands (major ones) with the explination
Cerci Lanister same question
How do i make am acro that copyes the data of the excel i'm currently in,
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
1338, I copied thos code exact and it comes up subscript out of range
Excellent
Thanks you so much! What is the code to copy and paste from multiple workbooks??
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.
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 for share
Hi in this code if i want to give the workbook name baded on the cell value how can we do
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?
Thanks for the video, just want to ask how about a vba for different workbooks name?
What if I just want to transfer 1 selected row to another workbook?
Thanks
Useful and wel explained, thanks for making this available for the community. I have an issue using .PasteSpecial Paste:=xlPasteValues then .PasteSpecial Paste:=xlPasteFormats, I have a workbook where I need to copy a range "C4:C470" from current workbook to a new one.The range has conditional cell coloring rules (R then rd, b then blue.....) VBA does the job but when I visit the new file, already in windows I get "preview not possible, opening the file, I get "We found a problem do you want to repair, Yes then get "Removed feature conditional formatting....." then file opens with values but not the formatting. Have you come across this type of issue? Thanks for your help
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 ?
amazing
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?
How can I do this with multiple files that aren't open? I have 300 templates to do this with a fixed range for all templates.
Dear Jon, I have files named after contract number in folders, I want to write a macro so that particular row having contract number, copied in another workbook so I can have master data of all the files, can you please help me. I will be obliged its sort of urgent project with a short timeline from the management.
Great video tutorial, but my columns are not the same size in the destination file.
Can you please help me with the coding that how to MOVE and COPY the whole spreadsheet to a separate excel file
Jon I just stumbled onto this video after spending over 12+ hours of watching multiple UA-cam 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.
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
Hi. Thank you so much for your videos. It helps a lot.
Can you please help me with the below query.
If we need to select specific columns from one workbook worksheet to other workbook. For example excelA is having A to F, then I need only A,C and E columns data to be copied and pasted in other workbook. Please help me.
Nishat Nishu use advanced filter with action copyfilter and set your output headers to the desired ones.
When copying a row from one sheet to another I want to paste into Column B, the code works upto the last line wsDest.Range("B" & lDestLastRow) it highlights .Range and says invalid use of property
Any advice?
did you ever find an answer to this? I have the same situation, thanks in advance!
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. :)