Excel VBA: Copy Data Between ANY Two Sheets

[@Excelmacromastery]

You can download the source code from here: bit.ly/2uIlmeD If you plan to copy data using VBA then this article has everything that you need: excelmacromastery.com/excel-vba-copy/ Enjoy!

[@gunasekar4098]

I need one excel vba code User have Download rawdata from ERP and saved in c drive folder Macro should open and copy paste it data in my active xlsm file. Here I need to find without open and edit rawdata, macro should give message box If user have to open raw data file or edit the file.

[@abdel-fattahmaghari9806]

thanks Paul but the range is dynamic is so better than specific range when i add every time the data automatically transfer data without i have to change the range into code

[@richardgreves6161]

Web server is returning an unknown error

[@DeVibe.]

You said no macro recorder required, yet the whole thing IS a macro. 🤦‍♂️

[@jimfitch]

I started using assignment instead of copy-paste ~6 months ago after learning about it in your “make your code run faster” lesson. It’s amazing ... & blazingly fast! I’ve been updating my workbooks to replace copy-paste as I come across them, & I still react with wonder at how faster - & simpler - the code is. Thanks!

[@Excelmacromastery]

Thanks for the feedback Jim. Glad you like it.

[@thanatos767]

@@Excelmacromastery I keep running into various issues when using assignment, because I don't think I knew about the resizing needed. If you use the codename of the worksheet can you forgo the workbook name?

[@joaocustodio2094]

That's impressive. In all the videos I always learn something new. Even when I thought it was not going to happen. Thanks very much Paul.

[@fazlanmohomed7756]

It's Amazingly all copying needs in one place who have the bid knowledge of coding. You emphasize it a great manner. thank you!

[@sasavienne]

Your tutorials are always helpful. It is easy to notice that you are very professional.. 🌟 🌟 🌟 🌟

[@Excelmacromastery]

Thanks for your compliment K D

[@likhangsubok]

thank you so much for a very very useful tutorials i learnt a lot , it is very timely for my office automation project using excel vba. I did a lot of systems in our company by just doing the research when i need some codes. Now i can do it by my own with your very clear explanations of copying data from different files and sources. now I am one of your followers. keep it up and thanks for sharing a lot of videos.

[@frikduplessis3869]

Again Paul this is a very good example, of a common problem everyone experience some or other time during the learning period, brilliant 👏👏👏

[@Excelmacromastery]

Thanks Frik

[@chaitanyas2293]

No insanity pure knowledge... Thank you.

[@akshayc7152]

Hey Paul, you have the technics which i think i can never find anywhere else..you are really awesome. Thank you..u r really great👍

[@Excelmacromastery]

I appreciate that!

[@Outr8js]

Your video is the best in explaining how to use copy/paste in vba. How can I modify the vba code to copy different ranges in different worksheets and are in different columns and rows? Thank you and keep up the good work!

[@scottgaines2677]

Thanks Paul, you made copying data very easy to understand! Appreciate it!

[@Excelmacromastery]

Great to hear!

[@ALVINSKLUI]

Clear and concise explanation, very useful and can be applied immediately without any questions. Looking forward to other videos. Thanks!

[@FlowerCityCards]

Great explanation - there's such a large community on here and you're all such a wealth of knowledge!

[@kapibara2440]

This is a high quality tutorial, thank you. Surely I will be your subscriber 😃 I ve done tiger spreadsheets stuff, very good for beginners, now time to może up to intermediate and advanced vba guy 🤗💪

[@davemason8645]

This came at a great time, just as I was trying to figure out the best way. Thanks so much!

[@TheTyrori]

Finally, a tutorial that actually runs and compiles when I use the code! Amazing. Thank you. xD

[@Excelmacromastery]

Glad you like it

[@TheTyrori]

@@Excelmacromastery For sure. All of your stuff has been helpful so far! :)

[@PortableProfessional]

Such a useful video and so easy to follow along. Thank you!!

[@Excelmacromastery]

Thanks. Glad you like it.

[@AI-ec2qb]

Paul is a genius

[@ghspublicwazriabadghspubli3779]

Sir your tutorials are very useful and also helpful.

[@lucasmoreira7680]

That's exactly what I was looking for! Thank you so much!

[@JonathanExcels]

I didn't think that the by assignment method would convert formulas to values but.....it does. Thanks.

[@Excelmacromastery]

You're welcome Jonathan.

[@susantakumar8330]

Thank god... Finally one video where I got a validation to my knowledge of VBA.. I knew everything whatever you have explained.. keep sharing. Bcz many topics I used to note down so that I will not forget..

[@gunasekar4098]

Hi sir, Really very very useful your video and code. Same order I will do one automation in excel workbook.. could you please help me. How to approach you. Kindly advice

[@michaelDoherty-tb1mn]

Hi, resolved my below query the workbook had a space after the last letter, this was causing he runtime error. all working great and so much simpler than other options. Can you advise if you have a tutorial on how to select next empty row that can be integrated into the VBA. I am a real VBA novice and your videos are really helping me develop my skills

[@shafiquek9960]

Very nicely explained Keep sharing your expertise and keep doing good work Kudos

[@Excelmacromastery]

Thank you, I will

[@jamesstawecki752]

Paul - Great stuff. Super informative. I have something I have been trying to figure out and this video addressed 1/2 of it, wondering if you could solve the other 1/2 also. I have sheet 1 "Buy or Sell " where I have a list of items. I want to be able to add or remove a number to a total on Sheet 2 (stock), but after I add or subtract, Clear the contents of the number bought or sold on sheet 1, without clearing the list item, just the numbers. SO on the Buys or Sell sheet Row 1 would have headers Items, Buy, Sell And Row 2 would have Oranges, Blank (or Zero), Blank (or Zero). If I buy 5 oranges and hit a button "submit" i want that 5 added to the total of the oranges on the Stock tab - BUT - then clear the purchase on the Buy-Sell tab - without reducing the number of the stock - and then be able to re-use and readd. so I could get 5, then reset and get 3 more, reset and remove 6, reset and add 10, and so forth. any help you could provide would be greatly appreciated.

[@amarnadhg6896]

Perfect VBA. Keep us posted

[@Excelmacromastery]

Thanks Amar.

[@shabustinkslol]

Fantastic! I've been slowly improving my copy paste macros the past few years, my early ones used copy-paste with the clip board like your last example, but as I've been improving them continuously my base code looks very similar to your first example, but yours is even neater and quicker. Very nice!

[@Excelmacromastery]

Glad it was helpful!

[@trielly2608]

Great video and explanation. If I want to continue to add data from the source worksheet to a different destination workbook, how do I copy paste data from the source worksheet to the next blank row in the designation workbook? Thanks.

[@sureshkumar-pg1ww]

very easy and useful sir. thank you.

[@ronaldratuwongo6913]

Hi Paul, first I would like to thank you for some of your videos that helped me to create my own VBA coded workbook. I'm now have one task that I would like to ask, could you please help me on how to format a database (table) into invoice format look like, with some of the items are consist of various items (can be one or more than one). Thank you in advance.

[@stevennye5075]

Excellent explanation!

[@Excelmacromastery]

Glad it was helpful!

[@muhammedasrar8773]

Hi Sir i dont know the range values in some of my sheet.. how to select the values dynamically in range based on the number of rows and columns available in that particular sheet. Thanks Asrar

[@BruceKing-pf7xi]

Hey...great video. Am trying this out & finding that I am copying the formulas from one sheet to another instead of just the values?

[@wayneedmondson1065]

Awesome lesson Paul! Great run through of a variety of methods and examples. Easy to follow.. great for learning! Thanks for sharing :)) Thumbs up!!

[@Excelmacromastery]

Glad you liked it Wayne!

[@muzguz7276]

As always, extremely well explained and set out. Thanks very much.

[@andresbambarenalcala8866]

Good morning, excellent, thanks for the video, and how would I change the code if I had to copy discontinuous ranges, for example data from one row, column A, column C and column E to the next sheet in row 1, columns A, B and C

[@alessiatangpropertyconsult5661]

Thank you 🙏 it easy to understand

[@vadymroz2774]

I love your channel! I keep returning to it again and again. Big thank you! Is it possible to use assignment to assign values from a closed workbook to the open workbook, but without opening the closed workbook (i.e. to avoid workbooks.open altogether)? Is there a way for excel to reach into the data stored in the workbook without opening that workbook? Thanks in advance!

[@conradblume4274]

Love these videos!! I always learn something new!!

[@Excelmacromastery]

Thanks Conrad.

[@javedahmed8343]

Very short and simple

[@michellebarip9259]

You're AWESOME!! Found this VERY HELPFUL!! Thank you!!🙏🏾💯

[@Excelmacromastery]

You're welcome Michelle.

[@mrashid229]

Dear Paul, I am grateful to you to have learnt so many amazing features of Excel VBA from your videos. Just realized that its possible opening an excel file using GetObject() instead of Workbooks.Open() method. This way the file is not made visible on the screen but it appears in the Project window in the VBE. Written below some concise code to explain my experience. Do you have any suggestion/opinion in opening file using GetObject() in the present context? Set rgSource = GetObject("D:\MyFiles\otherfile.xlsx").Worksheets("Report").Range("A1:B15") ... rgSource.Parent.Parent.Close 'Closes otherfile.xlsx

[@alanrichardson6383]

Thanks for this so much. Just wondering if I needed to keep a track of an entire row in a worksheet that was built by input from another worksheet - like for reporting purposes - how could I do that?

[@querrythis]

Your video are so well done. Subscribed.

[@alterchannel2501]

I love your videos, thanks a lot for sharing. I have a question, can you do the same thing but NOT specifing the name of the sheet but using its Code Name, for exmple: ThisWorkbook.Sheet1.Range("A1") ..... so even if someone changes the name of the sheet it still works? I've tried but it gives me an error if I don't specify the name of the sheet

[@Excelmacromastery]

Yes, you can use the code name like this codename.Range("a1") But the sheet must be in the same workbook as the code.

[@albertbatfinder5240]

Always like the clarity and completeness of your videos. However...(!) ... I have found the copy/paste fails with very large datasets. Is the assignment method more efficient? Also, it might be instructive to show how to hide any screen flicker, Also, as well, is it good practice to clear the clipboard after a copy/paste?

[@mcallister7593]

Excellent again. However, I tried to use assignment instead of copy/paste to copy data from one Excel listobect to another. In this case the "resize" step gave me some trouble, and without resizing the assignment line produced an error saying the method is not supported.

[@alexxgalaxy]

Very useful! The assignment definitely works faster than copy and paste. There is one problem, though. I tried to transfer a range of data. In which, the value of some cells is "03"/"00". The value of these cells in the destination sheet became 3 and 0. Might I know how to transfer the original value?

[@sksanket]

Hi Paul, I must say excellent contents and great way of explanation skills.... keep sharing knowledge it will definitely help people's like me. Another thoughts is it possible to creat video on how to download attachments from perticular subject mail or perticular attachment name. Also SQL videos are most welcome. Sanket

[@mike_case]

Thank you Paul :) Great example for beginners :)

[@Excelmacromastery]

Thanks Michal

[@Msdkash]

If you have any video on how to use offset criteria, it would be very useful

[@Tina009130]

Thank you very much for sharing! I am looking forward to more simple but practical examples for beginners like myself. :)

[@Excelmacromastery]

Glad you like it Tina.

[@tramateia]

Is it possible to write a code that catches a growing source table? For example, each week I extract a new table from our software. And it gains new lines each week. Can I write a code that encompasses the new lines by itself? Thanks for the video!

[@keithturner2889]

Another great video Paul. One question though how do you copy data including columnwidths and formatting eg conditional formatting?

[@Excelmacromastery]

You have to use the PasteSpecial function of Range.

[@OmerOmerBenOmer]

Thanks for all

[@Thelalligel]

Thank you very much....great video!

[@felipedeamorimcarvalho8750]

Great explanation!! Even do i don't use a lot of VBA codding I love to see your videos.. I like to see a lot of possibilities.. What about if you have a set of files? Is there a smart way to read and copy from all of them?👍

[@glenhenryoconnor3370]

fantastic allway get great tips from you.

[@jacek1366]

It's great knowledge, thanks!

[@ahmadswerki2706]

that's awesome, I really appreciate your efforts.. This approach solved most of my problems, I send my respect to you from Syria.. Is there a way to copy data from another workbook without opening and closing the file??

[@Excelmacromastery]

Glad it helped! There's a few ways to to do it. Check out my video on ADO to see how I used this to do it.

[@jonaslinchangco824]

I'm learning alot from this channel. I'm currently making an invoice/POS/Database for our welding shop. I Have a question. What code can I use if, let say, Company in ColA is always updating?

[@Excelmacromastery]

Do you mean you want to copy when the column updates? If that is the case then you can use a worksheet event to trigger the copy(see my video on worksheet events here: kzbin.info/www/bejne/iorcgaWnpquajMk)

[@anwarkhurshid6152]

@@Excelmacromastery The link doesn't work though.

[@williec1710]

This is awesome, only problem is I'm not getting the CODE when i click on your link provided. i only get a zip file with data in the workbook, but no VBA code.

[@krn14242]

Thanks Paul. Good stuff.

[@zingfam7616]

Can you explain how to copy and paste with multiple column ranges. eg. A:D, F, AW, AI, AE. I am not able use your code and I also want dynamic without need to put range to last rows. Thank you.

[@mfp8078]

Excellent, Keep it Up

[@ibrahimezz4317]

This was simple easy flexible amazing

[@sanjibpramanik9268]

I always use assign range values to another to copy data as you shown here at last, coz it runs faster than using copy and pastespecial. The new thing I learnt from this video that resize the range method, I used to find out last row of source range and using that variable to set that destination range, but now I can use resize mathod, and don't need to use that variable. Thank you so much sir 🙏 Sir, is there any way to store variables value in memory even my computer is shut down? Actually I've a large code that take approx 10hours to run, and I want to run that code seperate times, but want to run that code next time where I stopped it last time. Is there any solution for that? Thanks in advance 🙏

[@Excelmacromastery]

Thanks Sanjib. Glad you enjoyed the video. When the code stops running then the variables are no longer in memory. You have to store them in a file if you want to access them after the computer is shut down.

[@sanjibpramanik9268]

@@Excelmacromastery I'm also very glad to get your reply, thank you so much Sir 🙏 I always enjoy your videos, coz there is must be something new to learning. Recently I got the clear knowledge about 'byRef, byVal' by your video, very clear explanation with very simple example and also very simple english with very clear pronunciation make your video enjoyable and knowledgeable. I'm grateful to KZbin to give me suggestions that I found this channel. Love you from India ❤️🧡💛💚💙💜❤️🧡💛💚

[@MrDavehall19]

BIG question what if my row count is not fixed. What if the rg.Source row was different each time i tried to do a copy and not fixed.

[@nicholascarsner3266]

Hey Paul - love the channel and demos! I am currently working on a project where I need to automate creating individual tabs for records Owners based their name. I used to have a functional solution but an update disabled it. It seem like some of your methods would yield faster results and simpler code. Is there a way to couple autoFilter dynamics with loop tab creation to accomplish this?

[@TheeLazer]

how do you do similar for noncontiguous ranges as the source?

[@SirChiefUK]

Hi, can a row be copied to another worksheet based on criteria from source worksheet?

[@JKittles]

Just finding this video trying to figure out VBA. Can this be used to "Cut" data into another worksheet instead of "Copy" the data? Thanks.

[@AlexanderEskandari]

For example, if the table was ongoing with more rows of data always being inputted, is this process still possible.

[@sasavienne]

Thank you Paul..

[@Excelmacromastery]

You're welcome.

[@bradosborne9299]

Could you save the screenshot, of a table, to save as a PNG or JPG, and save it in a folder?

[@edge5817]

Thank you Paul... this is cool!

[@Excelmacromastery]

You're welcome Edmunda.

[@ptaker86]

Can I copy with this method whole sheet from one xlxs to another?

[@walterelemino]

Thank you Paul, this is very useful to my project. Hope you will have vba excel tutorial using Mac.

[@michaschonewille9209]

Great video thnx alot i'm wondering is it possible to replace the Excel sheet name with the VBA sheet name ?

[@gabiold]

You mean the codename? Absolutely possible, even more recommended!

[@BenjaminHouot]

Seems very simple code compare to your other videos :( But keep posting, U're the best ! :)

[@SolidSnake59]

Yes, but it's good repetition for beginners. To be honest I watch Pauls's video for fun. I just like to watch him programming :)

[@Excelmacromastery]

Thanks Benjamin. I deliberately kept the code simple to illustrate accessing different worksheets.

[@kpsrathore]

Great stuff. Just one question. Is it possible to copy only specific rows and not the entire range based on specific criteria?

[@Excelmacromastery]

I'm not sure what you mean? Do you mean copy Columns?

[@kpsrathore]

@@Excelmacromastery actually I use Excel to prepare the salary for my workers. There are people on weekly and monthly rolls. But I maintain a common excel sheet for the attendance for all and then manually copy data at the end of every week so that I can pay them. At the end of the month I calculate the salary for my monthly workers and pay them. I need to know how I can copy the data for the weekly workers in one go and then do the necessary calculations. I would like to do the same for my monthly payments.

[@chsproteus5770]

This does not work for me, I have been going around in circles doing everything in this tutorial and it just does not work. I get an error on the rgDestiantion.Pastespecial xlpastevalues. I looked at your examples and copied it exactly the same and still it will not work and gets hung up here. Any ideas what could be the issue? [SOLVED] For some reason when i changed to xlpasteall it worked.

[@grahamparker7729]

Another great video.

[@wanrids]

hai. may i now what type of font you are using in this micro?

[@nairrs6030]

thanks for the guide.love from god's own country.

[@malap123]

How to copy paste with source formatting if we do it randomly based on conditional loop?

[@SanjayKumar-go9iz]

How we can copy specific data like some specific month from one workbook to another.

[@lloydbelle3406]

Please can you help me with this. I going round in circles and hitting the same 1004 error at the same point no matter what method I use. I love the source to destination technique as there is no need to make sheets active. Basically I am copying selected columns from one datatable on one sheet and pasting the selected columns to form a slimmer datatable on another sheet. For HDR = 1 To LSTHDR HDV = Cells(HDR, 1).Value CPYC = Application.WorksheetFunction.Match(HDV, INP.Range("A1:Z1"), 0) PSTC = PSTC + 1 Set RngSource = ThisWorkbook.Worksheets("Input").Range(Cells(1, CPYC), Cells(LDROW, CPYC)) Set RngDestination = ThisWorkbook.Worksheets("Output").Range(Cells(1, PSTC), Cells(LDROW, PSTC)) RngDestination.Value = RngSource.Value Next HDR The error line is Set RngSource =ThisWorkbook... All of the variables exist in the local window at this point ie CPYC (Copy column) and LDROW (last data row), so I know its' not the variables, but I can't see anyting else. I have the whole code below so you can see that my variables are set correctly. I have been stuck on this for over a week now. Sub FlexiCopy() Dim OUT As Worksheet, INP As Worksheet, CON As Worksheet Set OUT = ThisWorkbook.Worksheets("Output") Set INP = ThisWorkbook.Worksheets("Input") Set CON = ThisWorkbook.Worksheets("Control Sheet") Dim HDRCNT As Integer, LSTHDR As Integer Dim HDR As Integer Dim HDV As String Dim CPYC As Integer, PSTC As Integer Dim LDROW As Long Dim RngSource As Range, RngDestination As Range LDROW = INP.Cells(Rows.Count, 1).End(xlUp).Row PSTC = 0 'XXXX CHECK FOR GAPS IN HEADER LIST XXXX HDRCNT = Application.WorksheetFunction.CountA(Range("A1:A10")) LSTHDR = Range("a11").End(xlUp).Row If LSTHDR HDRCNT Then MsgBox ("Check for gaps in Header Column") Exit Sub End If 'XXXX CHECK FOR EXISTING & UNIQUE COLUMN HEADERS XXXX For HDR = 1 To LSTHDR HDV = Cells(HDR, 1).Value If Application.WorksheetFunction.CountIf(INP.Range("A1:Z1"), HDV) = 1 Then GoTo HDVOK End If If Application.WorksheetFunction.CountIf(INP.Range("A1:Z1"), HDV) > 1 Then MsgBox ("Header Column: " & HDV & " is duplicated") Exit Sub End If MsgBox ("Header Column: " & HDV & " does not exist") Exit Sub HDVOK: Next HDR 'XXXX COPY & PASTE REQUIRED COLUMNS XXXXX For HDR = 1 To LSTHDR HDV = Cells(HDR, 1).Value CPYC = Application.WorksheetFunction.Match(HDV, INP.Range("A1:Z1"), 0) PSTC = PSTC + 1 Set RngSource = ThisWorkbook.Worksheets("Input").Range(Cells(1, CPYC), Cells(LDROW, CPYC)) Set RngDestination = ThisWorkbook.Worksheets("Output").Range(Cells(1, PSTC), Cells(LDROW, PSTC)) RngDestination.Value = RngSource.Value Next HDR Application.CutCopyMode = False CON.Activate Range("A11").Select End Sub

[@denisovan]

More efficiency / design pattern videos please.

[@ksmemon1]

FIFO Method or Function is Required to Calculate Invoice Age and Cost Purpose

[@campanate]

Hi! How can i set the macro to copy data every specific time? Let´s say, every 5 min it copies the data

[@ghspublicwazriabadghspubli3779]

Dear Sir How are you? can you help me sir? I want to become your student to learn VB. Sir i watch your video regularly. Regards TANVEER MIRAN

[@RandomName888]

Is the a way to copy from a range in another file without opening the file or using ADO?

[@Excelmacromastery]

Hi Jarrett. If you are using VBA you have to open the file unless you're using ADO.

[@jeffreyfischer2157]

Hi, i have a program where i need to make duplicates of 2 sheets at a time (because the links of 1 sheet point to the 2nd sheet, and if i do the duplication in 2 steps, the links will point to the original sheet). However, after about 20 duplications, i get out of memory errors from excel and the program crashes. Any tips on how to do this more efficiently? I need links to stay active, ie - i cant hardcode the values

[@mfp8078]

Dear Kelly Hi! On my request, I am in receipt of a couple of emails from you. I sincerely appreciate your prompt reply. Actually I am unable to understand or work with it as I have not yet come across with XML concept of MS Excel. Please upload related Vid or advise on how to become familiar with XML. With regards, PM

[@dh6167]

How would I do this but copying the row only if the date = Today() and only after a certain time, paste as values in same row

[@sasavienne]

Just amazing. 🔆 👏

[@Excelmacromastery]

Thanks. Glad you like it.

[@tpsdas]

getting some error. Plz help!!! My Sheet names are Cbal ( i.e. source) & Sheet1( i.e Destination) I want to Copy and Paste value ( Not formula ) from CBal Sheet to Sheet1 Sub CopyData_MODULE6() Dim rgSource As Range, rgDestination As Range 'set resource [workbook].[worksheet). (range) Set resource = ThisWorkbook.Worksheets("CBal").Range("A1:K15") Set resource = ThisWorkbook.Worksheets("Sheet1").Range("A1 ") rgSource.Copy rgDestination.PasteSpecial xlPasteValues End Sub

[@dwaynewilson3263]

I have created a Invoice sheet and what I am trying to do is have the customer information ie. name, address, city,state,ect. which are in different cells (not in the same columns or rolls) Saved to a second worksheet to create a Customer Database all within the same workbook. Could you help with this.

[@gabiold]

I would name the required fields on the invoice sheet, then in the VBA code, copy them one by one to the other sheet, practically to a table (ListObject). First, checking if it is there already. There is an issue, what to do when not all the data is matching for an existing customer. But I would do the reverse instead. Register the customers first, then on the invoice sheet, pull all the data, possibly using a customer ID. You don't even need VBA in this case.