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!
@DerickPitcher2 жыл бұрын
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.
@MsQuikly4 жыл бұрын
I think you just saved my internship!!
@im_shaded3 жыл бұрын
word bro LOL
@alexgarcia14865 ай бұрын
same hah
@18_moch.januarakbar173 ай бұрын
same hah
@firststep77503 жыл бұрын
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.
@norbertfris60185 жыл бұрын
Thank you Jon! You've just helped me to become a successful man!
@darrelljustinlopez40252 жыл бұрын
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.
@jrm5234 жыл бұрын
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.
@joshuabrockman74673 жыл бұрын
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!
@nathanaelmouckagni71652 жыл бұрын
I am so happy with the content of this tutorial, thank you very much Excel Campus !
@mikevanwieringen98832 жыл бұрын
This tutorial shows exactly what I'm trying to achieve with an Excel project I've started recently...Brilliant ! ;-)
@Akarsh612 жыл бұрын
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).
@FlowerCityCards2 жыл бұрын
Very useful & I've incorporated this into my latest video where I've created an awesome tool for the average side-hustler. Thank you!!!
@MohamedRAMDANI-ri4jp7 ай бұрын
thank you very much as a newbie at VBA it captured my mind and I got passionate about thank you for your videos
@ExcelCampus7 ай бұрын
Happy to hear that! 😀
@ShaneNickers2 жыл бұрын
This helped me so much at work. Thank you!
@NeoclassicYT2 жыл бұрын
hmm... DUde, what to say this was actually some instructive information! Thank you for explaining the basics!!!!! ❤👍
@MaStEr994ful3 жыл бұрын
Great job, saved me a lot of time! God bless you
@alperak8580 Жыл бұрын
it's fantastic dude thanks you, thanks a lot greeting from Istanbul
@alperak85804 жыл бұрын
So comprehensive, so successful Learn VBA with Jon Thank you bro, thanks a lot!!!!!!
@guidobusuttil55564 жыл бұрын
Thanks Jon. Very comprehensive
@a.j.wilkes63525 жыл бұрын
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.
@ExcelCampus5 жыл бұрын
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. :-)
@ClintSevilla3 жыл бұрын
I am trying to figure out how to do that. Any references to point me to?
@a.j.wilkes63523 жыл бұрын
@@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.
@tuntunsaw72424 жыл бұрын
Thank you. You make my life easy to live.
@shoebkhan7113 жыл бұрын
Thank you so much. This is very helpful
@mbhagyasri883 жыл бұрын
Great video and easy to understand 🙏🙏
@brandyhenderson2 жыл бұрын
Jon, you’re my new best friend!
@brandyhenderson2 жыл бұрын
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
@Juan-Hdez9 ай бұрын
Very useful. Thank you!
@tamilselvan20884 жыл бұрын
Thank you for your good work
@mohammadshakeelsharif3452 жыл бұрын
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
@nkcf48892 жыл бұрын
Great Video! How would you copy and paste above an existing range?
@jam05eun2 жыл бұрын
Thanks! This is helpful. May I know if you have tutorial that will show how to copy only filtered data? Not all data
@mohamedgenidy19013 жыл бұрын
THANK YOU VERY VERY MUCH FOR YOUR PERFECT HELP
@juhainaahmed60903 жыл бұрын
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?
@juhainaahmed60903 жыл бұрын
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
@wayneedmondson10655 жыл бұрын
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!
@manojnath94083 жыл бұрын
Great tutorial
@hope19225 жыл бұрын
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?
@krishnendughosh23483 жыл бұрын
Did u get it
@violetnha2 жыл бұрын
So excited to use this!! :)
@nsanerydah4 жыл бұрын
Jon I just stumbled onto this video after spending over 12+ hours of watching multiple KZbin 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.
@byakb18688 ай бұрын
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.
@selvan1264 Жыл бұрын
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
@mokongx3m Жыл бұрын
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?
@undrawingend2 жыл бұрын
This has just saved me an hour or more of copy and paste from sheets !, is there a way that this could be used to copy from a range of sheets that all have the same name but different date - eg 11.04.22 do not use to 16.04.22 do not use ? I have a sheet with the date range that is calculated based on workdays and need to collate the data from the 5 sheets and needs to be run each Monday for the previous week I m thinking that the name section could be “* Do Not Use” but this would pull sheet data from more than needed
@rishabhsaxena98583 жыл бұрын
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.
@connorloze541810 ай бұрын
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?
@rziguiaymen75194 жыл бұрын
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 ;)
@nishantong6884 жыл бұрын
Excellent contents, Thank you!
@shaikhmohammedriyazbasha73312 жыл бұрын
Dear Sir, your VBA is excellent, but I need a code for pasting a text in the specified cell of another work books (650 qty) in a folder through a message box (=should ask range) while pasting.
@helenshanmugam37685 жыл бұрын
Thank you for the very detailed and precise video and code. I managed to solve my problem.
@yogeshbhat17 ай бұрын
Thanks a lot. You made it look so easy.
@ExcelCampus7 ай бұрын
Glad it helped 😀
@pipo441 Жыл бұрын
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.
@Buenomars3 жыл бұрын
Instead of pasting below the last row cell, what should I change for pasting right next to the last column cell?
@nandogarza78413 жыл бұрын
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...
@markjosephlobete2563 Жыл бұрын
Thanks for the video, just want to ask how about a vba for different workbooks name?
@matthewfawaz22474 ай бұрын
Hi, thanks for the great video, I am copy pasting info to a file where I am unable to access some squares and a block message pops up, is there a way for the VBA to bypass this and continue running the code? Thanks!
@aymanejoud8398 Жыл бұрын
Thank you Mister
@beniedik7 ай бұрын
hi, the video is good but I have a question. what can you do if the workbook that you are copying from changes the name or the file format? thankyou
@lotto0094 жыл бұрын
Thank you for share
@oumezianeimededdine69804 жыл бұрын
Brilliant ! Thank you so much
@GuruDanny4 жыл бұрын
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.
@ibrahimasgarli73854 жыл бұрын
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.
@srikanthchitturi72832 жыл бұрын
Hi, Great video. I have a question, I have the similar scenario. I have a table with data and I need to clear the data before adding new data from other workbook, I am able to do, but, while deleting, it leaves one row and when we copy new data, it leaves the first row as blank. Not sure, how to solve this. If you could help me, it will be great. Thanks in advance.
@MultiBodmin5 жыл бұрын
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.
@ohdjrp45 жыл бұрын
Thanks to Excel Campus!
@RS-wk1cz3 жыл бұрын
Really, very good.
@nosrednawyt2 жыл бұрын
Great vid! I need to copy and paste info from 4 worksheets inside a workbook to MULTIPLE workbooks that are basically identical to the one I'm using to copy the info from, I just need to update some formulas. The problem is, I need to do this with 300+ workbooks, is it possible to bulk copy and paste all this info to the specific range I need in an automated way ? I was doing it manually, but it's taking forever, and I need this done ASAP for work, can someone help me out ? thnxxx
@marcelitopacaldo99522 жыл бұрын
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.
@corysmith45692 жыл бұрын
How do you hot key a macro without using the record feature? I always have to record first, then go in and use that sheet before I start coding. Will the mode be dependent on workbook? or can transfer the script over to another computer and have it run there with the same hotkey?
@Ke-D2 жыл бұрын
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.
@huynhdang24414 жыл бұрын
Thanks you so much! What is the code to copy and paste from multiple workbooks??
@jonnabelleyulo1174 жыл бұрын
Thanks Jon. Very useful. How about copying the data and pasting it in the other worksheet depending on the date today
@keshavkeshu5321 Жыл бұрын
Hi sir Copy/paste below last cell in destination workbook can we create for different sheets data for this copy paste below last cell
@ka-techarastrmac6334 жыл бұрын
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...
@eliassal1 Жыл бұрын
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
@daudapriwaluyo984 жыл бұрын
Why i cannot run the very first copy (open thype) it keeps say "the object doesn't support this property"
@056-m5o2 жыл бұрын
Hi how can we insert row with some data in same sheet Example I have make a table and I want to insert same table below it by clicking one button I hope you can answer me
@indrakumarr47562 жыл бұрын
Hi Jon , Is there a way to insert an existing excel template using VBA code.for example i have a excel template already i have written query for some work after that I need to use the existing excel template in the same macro..
@petetartaro2 жыл бұрын
Say I have multiple data sheets and I want to copy each of them into a separate output workbook. Is there a way to automate copying each one into a separate templated workbook and save them separately?
@UTEBAYEVKAIRAT2 жыл бұрын
Hi Jon, can you please advise a solution if a major wbk is open but DS worksheets in workbooks are closed? I've one major wbk, and multiple workbooks and each workbook has multiple DS worksheets. so, the process is: the major wbk is open. macro command goes through each row, by opening a closed workbook, open DS worksheet and then paste data from major wbk to DS worksheet, goes to row below open another DS worksheet paste data, save and close workbook. goes row below open another workbook et c. The Major wbk has a reference to workbooks and worksheets. thanks!
@altafbaig64854 жыл бұрын
simply excellent !!!
@jokiab5 жыл бұрын
Thank for the video Jon! It was really good explained. I have a question I hope you can answer. Let’s say that the workbook you are copying from changes name. How will that be updated automatic? E.g. Let’s say the code runs in your Reports.xlsm. You then have 3 workbooks in a folder: New Data1.xlsx New Data2.xlsx New Data3.xlsx The name of the workbooks will appear in cells in workbook report.xlsm. Like in A1 we have "New Data1.xlsx". A2 we have "New Data2.xlsx". A3 we have "New Data3.xlsx". How will the code: Workbook("New Data1.xlsx") automatic change the name for a new workbook given by a cell? Let’s say, that the name of the data workbooks is dynamic? Thanks
@jokiab5 жыл бұрын
@@bukharrysafri9399 hey dude, I have not. But I'm working on it. I can let you know if I find a solution?
@smith28c4 жыл бұрын
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
@Rachad23175 жыл бұрын
Thanks Jon for the helpful tips. Putting it to work right away :)
@ExcelCampus5 жыл бұрын
Awesome! Thanks Rachad!
@oguzhanalp54563 жыл бұрын
amazing
@SnehaJayal5 жыл бұрын
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?
@amittiwari-gw7mm3 жыл бұрын
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?
@pragas4288 Жыл бұрын
How do we bind values in column wise, ? Like find empty column and paste the value..
@tannbunny19922 ай бұрын
Thank you for your sharing
@ExcelCampus2 ай бұрын
Thanks for watching! 😀
@ujangsonjaya8348 Жыл бұрын
Excellent
@wasifabbas81603 жыл бұрын
Hi, Its really good video but i have one question. Actually, i want that user specify the worksheet through variable, is it possible. As in the video at 12:38, worksheet index changed manually or statically with 1 as workbook have only 1 worksheet. My query is that is this part can be replaced through variable. Code ask user to specify the worksheet number, which need to be copied and later on, call it by variable..
@md.saifulislamtuku93035 жыл бұрын
Sir, I think I created a document and gave it to one person that he would not be able to do that document after a given date or time. If the system of such lock can be done in "XL". If it is, then it will be beneficial if you create a tutorial on that topic. Thank you
@aarondumapias6084 Жыл бұрын
Hello! is there a way to copy paste the selected cell only in another workbook?
@rsarunprakaash4 жыл бұрын
What should we use in macro if we want to copy a set of data from multiple workbooks. Here in Eg. we have "new data.xls" , how about if i have to copy from another 100 workbooks to report.xlsm
@ushaa10262 ай бұрын
How do I copy specific person data from one worksheet to another worksheet??? Also with varying range.
@hemangis43512 жыл бұрын
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)
@MARCUS-mj9vf3 жыл бұрын
Hey, The code runs smoothly but I just want to copy columns A and AE.. What changes do I have to make in the code line wsCopy.Range("A2:D" & lCopyLastRow).Copy _ wsDest.Range("A" & lDestLastRow) Please suggest.
@thebesttechnical36085 жыл бұрын
Thanks for sharing a good and fast way.
@Sridevi-ht9nj8 ай бұрын
Thanks
@NiteRam5 жыл бұрын
What if I just want to transfer 1 selected row to another workbook?
@AliTwaij5 жыл бұрын
Brilliant thankyou
@TheGamer-we2br Жыл бұрын
Perfect brother
@ExcelCampus Жыл бұрын
Thanks, @TheGamer-we2br ✌️
@aydeediaz23072 жыл бұрын
Hi, I really like your videos. I was following this copy and paste method and for some reason I have this message Run-time error 438 Object doesn't support this property or method Do you know how can I fix this? Or why is happening?
@lilycheong38324 жыл бұрын
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.