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!
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.
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
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 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?
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.
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!
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
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 🤗💪
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.
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.
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
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
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.
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!
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
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?
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
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!
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.
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!
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.
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?
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.
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..
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
@@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.
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?
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: ruclips.net/video/TYyPrqqFgVg/видео.html)
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?
i love the simplicity of the code vs other online tutorials, however the code wont run past the Set rgsource = ThisWorkbook.Worksheets("Hen").Range("A12:BA35") stage, due to a run time error! what am i doing wrong?
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?
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??
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 🙏
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.
@@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 RUclips to give me suggestions that I found this channel. Love you from India ❤️🧡💛💚💙💜❤️🧡💛💚
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
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.
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.
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?👍
I have used the above, changing source and destination file names, and the data copies over to the new spread sheet. However, the column widths differ and I cannot see all of my data. How can I make sure that the column widths remain the same?
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
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.
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
![I.N "HALLUCINATION" | [Stray Kids : SKZ-PLAYER]](http://i.ytimg.com/vi/n5B5q1Hwt_U/mqdefault.jpg)
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!
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.
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
Web server is returning an unknown error
You said no macro recorder required, yet the whole thing IS a macro. 🤦♂️
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!
Thanks for the feedback Jim. Glad you like it.
@@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?
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.
Such a useful video and so easy to follow along. Thank you!!
Thanks. Glad you like it.
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.
It's Amazingly all copying needs in one place who have the bid knowledge of coding. You emphasize it a great manner. thank you!
Thanks Paul, you made copying data very easy to understand! Appreciate it!
Great to hear!
Your tutorials are always helpful.
It is easy to notice that you are very professional.. 🌟 🌟 🌟 🌟
Thanks for your compliment K D
That's exactly what I was looking for! Thank you so much!
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👍
I appreciate that!
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!
Clear and concise explanation, very useful and can be applied immediately without any questions. Looking forward to other videos. Thanks!
Again Paul this is a very good example, of a common problem everyone experience some or other time during the learning period, brilliant 👏👏👏
Thanks Frik
Hey...great video. Am trying this out & finding that I am copying the formulas from one sheet to another instead of just the values?
No insanity pure knowledge... Thank you.
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
This came at a great time, just as I was trying to figure out the best way. Thanks so much!
Sir your tutorials are very useful and also helpful.
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 🤗💪
Finally, a tutorial that actually runs and compiles when I use the code! Amazing. Thank you. xD
Glad you like it
@@Excelmacromastery For sure. All of your stuff has been helpful so far! :)
Love these videos!! I always learn something new!!
Thanks Conrad.
Great explanation - there's such a large community on here and you're all such a wealth of knowledge!
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.
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.
You're AWESOME!! Found this VERY HELPFUL!! Thank you!!🙏🏾💯
You're welcome Michelle.
Thanks for all
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
Very nicely explained
Keep sharing your expertise and keep doing good work
Kudos
Thank you, I will
how do you do similar for noncontiguous ranges as the source?
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
Excellent explanation!
Glad it was helpful!
Awesome lesson Paul! Great run through of a variety of methods and examples. Easy to follow.. great for learning! Thanks for sharing :)) Thumbs up!!
Glad you liked it Wayne!
As always, extremely well explained and set out. Thanks very much.
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.
Your video are so well done. Subscribed.
I didn't think that the by assignment method would convert formulas to values but.....it does. Thanks.
You're welcome Jonathan.
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!
very easy and useful sir. thank you.
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
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?
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
Yes, you can use the code name like this codename.Range("a1")
But the sheet must be in the same workbook as the code.
Perfect VBA. Keep us posted
Thanks Amar.
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.
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!
For example, if the table was ongoing with more rows of data always being inputted, is this process still possible.
Thank you 🙏 it easy to understand
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.
Paul is a genius
Another great video Paul. One question though how do you copy data including columnwidths and formatting eg conditional formatting?
You have to use the PasteSpecial function of Range.
Very short and simple
Thank you Paul :) Great example for beginners :)
Thanks Michal
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!
Glad it was helpful!
Could you save the screenshot, of a table, to save as a PNG or JPG, and save it in a folder?
If you have any video on how to use offset criteria, it would be very useful
Hi, can a row be copied to another worksheet based on criteria from source worksheet?
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.
Thank you very much....great video!
Thank you very much for sharing! I am looking forward to more simple but practical examples for beginners like myself. :)
Glad you like it Tina.
How to copy paste with source formatting if we do it randomly based on conditional loop?
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?
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.
It's great knowledge, thanks!
fantastic allway get great tips from you.
How we can copy specific data like some specific month from one workbook to another.
Can I copy with this method whole sheet from one xlxs to another?
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..
Thanks Paul. Good stuff.
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
Great stuff.
Just one question. Is it possible to copy only specific rows and not the entire range based on specific criteria?
I'm not sure what you mean? Do you mean copy Columns?
@@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.
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.
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?
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: ruclips.net/video/TYyPrqqFgVg/видео.html)
@@Excelmacromastery The link doesn't work though.
hai. may i now what type of font you are using in this micro?
Great video thnx alot
i'm wondering is it possible to replace the Excel sheet name with the VBA sheet name ?
You mean the codename? Absolutely possible, even more recommended!
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?
i love the simplicity of the code vs other online tutorials, however the code wont run past the Set rgsource = ThisWorkbook.Worksheets("Hen").Range("A12:BA35") stage, due to a run time error! what am i doing wrong?
Hi! How can i set the macro to copy data every specific time? Let´s say, every 5 min it copies the data
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
Thank you Paul... this is cool!
You're welcome Edmunda.
This was simple easy flexible amazing
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?
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??
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.
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 🙏
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.
@@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 RUclips to give me suggestions that I found this channel. Love you from India
❤️🧡💛💚💙💜❤️🧡💛💚
Excellent, Keep it Up
Thank you Paul..
You're welcome.
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
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.
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.
Another great video.
what if i do have multiple workbooks as source but only one destination
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?👍
I am running this code but getting error as object doesn't support this property or method
I have used the above, changing source and destination file names, and the data copies over to the new spread sheet. However, the column widths differ and I cannot see all of my data. How can I make sure that the column widths remain the same?
You have to either manually set them or write code to set them. You can also use PasteSpecial to copy column widths.
Is the a way to copy from a range in another file without opening the file or using ADO?
Hi Jarrett. If you are using VBA you have to open the file unless you're using ADO.
希望能有中譯版
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
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.
FIFO Method or Function is Required to Calculate Invoice Age and Cost Purpose
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
Is it possible to not hardcode the filename and pass it in runtime ?
Seems very simple code compare to your other videos :( But keep posting, U're the best ! :)
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 :)
Thanks Benjamin. I deliberately kept the code simple to illustrate accessing different worksheets.
I am trying to move rows from one sheet to another, but based on a criteria(i.e. starts with letter H). How can you add that criteria?