Hi Andrew. Awesome lesson! Packed full of good tips, tricks and techniques.. clear and easy to follow. Thanks for sharing :)) Love your videos.. especially VBA :)) Thumbs up!!
Hi Andrew, I really love your videos, I started discovering the universe of VBA recently and it was a blast finding your well structured and enlightening tutorials. I have a question please, I would like to copy data of the same format, from various workbooks that are dispatched in 2 folders into a new workbook (to be able to reconciliate these data and generate statistics afterwards) and I want to do so based on two criteria (i.e. copy only the rows that contain in their 3rd cell "pending information" OR "pending in the system"). I do this exercise manually weekly and it is repetitive and time consuming and I would really appreciate if you could help me with a code to do so. THANK YOU SO VERY MUCH !
Hi Naima, I'm so happy to hear that you've found the videos useful! I've just published a couple of videos which explain how to get data from workbooks and use criteria to choose which rows to copy. You might find the first video useful to give you an idea of what's possible ua-cam.com/video/4QuddU0GQlQ/v-deo.html The part which begins at 16:55 "Adding Criteria to a Query" explains how to choose which rows to include. The second video deals with multiple worksheets in the same workbook ua-cam.com/video/5F8q7Z9kWGw/v-deo.html And I'll be releasing some more videos this week which explain how to deal with multiple workbooks in the same folder. I hope that helps!
Hi Andrew, thank you so much for your kind help, I will definitely watch the videos that you suggested and I am looking forward to your other videos 🙂 keep up the amazing work ! A billion thanks.
Thanks for this - just one question. In order to be able to access the private Macro Workbook subroutines we go through the same add reference thing at the start, but after doing it there is a "references" folder added to the hierarchy. I notice that when you added the Scripting Runtime library to the project that didn't happen. Is there a reason why that is? It's quite handy as I can see at a glance whether I've imported all my macros into a new workbook or not Cheers!
Hi Sid! That's a good question - I know that when you reference another VBA project the reference appears in the References folder of the current project in the Project Explorer window. I also know that this doesn't happen when you reference a compiled object library from another application (.dll .tlb etc.). Up to this point I've never thought about why and have simply accepted that's the way it is! Obviously, VBA can tell the difference between the two types of reference and behaves differently according to which type you've selected but I completely agree - it would be helpful to be able to see the list in the Project Explorer window rather than having to open the References dialog box (as a small concession, at least the References dialog makes all checked references float to the top of the list). This has never bothered me up until now but I guarantee that it will annoy me every time I reference another library forever more!
Great tutorial Andrew, brilliantly explained. Is there a tutorial video of Wise Owl where VBA makes it possible to append (combine) all workbooks in a folder into one master workbook. I have got more than 600 workbooks I need to append one on top of each other (all column headers are same) but do want to do that with VBA rather than power query. I think it could be done with Dir function but also FSO. Would it be possible to shoot that kind of video. Many thanks. Denin
Hi Yasser, sure here's a link drive.google.com/file/d/1JLNfNuTrX2WFlUy2sNrO9RdqG3ydDVXo/view?usp=sharing And I've added it to the video description (although this works with any set of Excel files of course!).
Hi Andrew, I am very keen on VBA and have watched many / most of your VBA tutorials - they are so good!! I use vba occasionally in my job, but not really enough to keep improving... I've been learning on and off for a few years and recently built a sudoku solver, just for fun and in an attempt to improve my skills, which kind of worked. However, when I googled other solutions, they were so much more eloquent and efficient. How long does it take to get proficient and solving real world problems like in this tutorial?
Thanks Simon! It's a good question but such a difficult one to answer because it depends on so many factors. You're doing the right thing to reach that level of proficiency, however. Picking projects that are personal and fun like your sudoku solver is an excellent way to gain experience without becoming (too) frustrated. If you have the time (and I appreciate that you probably don't!), you could also try using VBA in your work even when it isn't necessary. Even then I don't think you'll experience that one, single, "I know kung fu" moment of enlightenment (although you'll have many smaller moments of clarity like that along the way). I'm unsure if that's helpful but stick with it, you're doing the right things!
@@WiseOwlTutorials thanks Andrew, I really appreciate your response. I’ll definitely try and use it more at work, there’s so much I could do it’s just a time issue... but I think the investment will be worth it. 👍.
@@WiseOwlTutorials can we create customised menu and sub menu in vbe menu bar which will generate codes automatically by clicking it ex: looping through sheets and copying data from one sheet to another sheet, looping through folders, creating sub procedure, functions with some logic in it. The main file which contains code will be in . Xlam format later can be shared with others. I am able to create customised menu in VBA but I have no idea how to generate codes from it.
@@vijayalakshmibadigeru4479 It's a complicated topic! Chip Pearson has the most detailed explanation of how to make the VB Editor write code into itself www.cpearson.com/excel/vbe.aspx It's a long read but if you reach the end you will know as much as I do about this topic. I hope it helps!
Hi Andrew, Please refer www.automateexcel.com/ once. I was talking about this kind of customised VBE to create my own custom forms and functions. Can you please tell me how they have created using what tools VSTO or Dotnet.
Thanks Andrew. Great tips on looping through multiple workbooks to combine sheets.
Thanks very much Kevin, glad you liked it!
Magic ! Thank you for sharing your knowledge here. Very helpful code. Thank you !
You're welcome, thanks for watching!
Hi Andrew. Awesome lesson! Packed full of good tips, tricks and techniques.. clear and easy to follow. Thanks for sharing :)) Love your videos.. especially VBA :)) Thumbs up!!
Thanks so much Wayne! Happy to hear you enjoyed it and thanks for the comments!
Great tutorial, nicely explained, thank you for sharing, Cheers
Thanks janez, glad you enjoyed it!
I really appreciate your tutorials. They are great;)
Thanks Enrike, I appreciate you taking the time to leave a comment, thank you!
Wonderful sir.
Thank you Rohith!
Hi Andrew, I really love your videos, I started discovering the universe of VBA recently and it was a blast finding your well structured and enlightening tutorials.
I have a question please, I would like to copy data of the same format, from various workbooks that are dispatched in 2 folders into a new workbook (to be able to reconciliate these data and generate statistics afterwards) and I want to do so based on two criteria (i.e. copy only the rows that contain in their 3rd cell "pending information" OR "pending in the system"). I do this exercise manually weekly and it is repetitive and time consuming and I would really appreciate if you could help me with a code to do so. THANK YOU SO VERY MUCH !
Hi Naima, I'm so happy to hear that you've found the videos useful! I've just published a couple of videos which explain how to get data from workbooks and use criteria to choose which rows to copy. You might find the first video useful to give you an idea of what's possible ua-cam.com/video/4QuddU0GQlQ/v-deo.html The part which begins at 16:55 "Adding Criteria to a Query" explains how to choose which rows to include. The second video deals with multiple worksheets in the same workbook ua-cam.com/video/5F8q7Z9kWGw/v-deo.html And I'll be releasing some more videos this week which explain how to deal with multiple workbooks in the same folder.
I hope that helps!
Hi Andrew, thank you so much for your kind help, I will definitely watch the videos that you suggested and I am looking forward to your other videos 🙂 keep up the amazing work ! A billion thanks.
@@NaimaEchalhi My pleasure Naima! I'm really pleased that you're enjoying the videos and VBA!
Sir Great !!!! i really enjoy but i have one question if i have multiple file with same data now need to in one excel tab using VBA.
Hi Pankaj, let me add that to my list and I'll try to get onto it soon!
@@WiseOwlTutorials Thank you Sir for accept my onion with prompt reply !!! You are very Humble Sir
@@PankajNegi007 Thank you for your kind comments Pankaj!
Thanks for this - just one question.
In order to be able to access the private Macro Workbook subroutines we go through the same add reference thing at the start, but after doing it there is a "references" folder added to the hierarchy. I notice that when you added the Scripting Runtime library to the project that didn't happen.
Is there a reason why that is? It's quite handy as I can see at a glance whether I've imported all my macros into a new workbook or not
Cheers!
Hi Sid! That's a good question - I know that when you reference another VBA project the reference appears in the References folder of the current project in the Project Explorer window. I also know that this doesn't happen when you reference a compiled object library from another application (.dll .tlb etc.). Up to this point I've never thought about why and have simply accepted that's the way it is! Obviously, VBA can tell the difference between the two types of reference and behaves differently according to which type you've selected but I completely agree - it would be helpful to be able to see the list in the Project Explorer window rather than having to open the References dialog box (as a small concession, at least the References dialog makes all checked references float to the top of the list).
This has never bothered me up until now but I guarantee that it will annoy me every time I reference another library forever more!
Great tutorial Andrew, brilliantly explained. Is there a tutorial video of Wise Owl where VBA makes it possible to append (combine) all workbooks in a folder into one master workbook. I have got more than 600 workbooks I need to append one on top of each other (all column headers are same) but do want to do that with VBA rather than power query. I think it could be done with Dir function but also FSO. Would it be possible to shoot that kind of video. Many thanks. Denin
Hi Denin! I think that you'll find this video useful ua-cam.com/video/7Jl7RMAsny8/v-deo.html
I hope it helps!
@@WiseOwlTutorials Thank you Andrew, yes it helps indeed!!! Many thanks. Thumbs up 👍
👍WOT is the best of
Ahh thanks!
Thank you very much. Can you share the files, please?
Hi Yasser, sure here's a link drive.google.com/file/d/1JLNfNuTrX2WFlUy2sNrO9RdqG3ydDVXo/view?usp=sharing
And I've added it to the video description (although this works with any set of Excel files of course!).
@@WiseOwlTutorials Thank you very much my tutor.
@@KhalilYasser You're very welcome!
Hi Andrew, I am very keen on VBA and have watched many / most of your VBA tutorials - they are so good!! I use vba occasionally in my job, but not really enough to keep improving... I've been learning on and off for a few years and recently built a sudoku solver, just for fun and in an attempt to improve my skills, which kind of worked. However, when I googled other solutions, they were so much more eloquent and efficient. How long does it take to get proficient and solving real world problems like in this tutorial?
Thanks Simon! It's a good question but such a difficult one to answer because it depends on so many factors.
You're doing the right thing to reach that level of proficiency, however. Picking projects that are personal and fun like your sudoku solver is an excellent way to gain experience without becoming (too) frustrated.
If you have the time (and I appreciate that you probably don't!), you could also try using VBA in your work even when it isn't necessary.
Even then I don't think you'll experience that one, single, "I know kung fu" moment of enlightenment (although you'll have many smaller moments of clarity like that along the way).
I'm unsure if that's helpful but stick with it, you're doing the right things!
@@WiseOwlTutorials thanks Andrew, I really appreciate your response. I’ll definitely try and use it more at work, there’s so much I could do it’s just a time issue... but I think the investment will be worth it. 👍.
@@ChetstarSimonA1 You're very welcome Simon, best of luck to you!
@@ChetstarSimonA1 I completely sympathise regards the lack of time! Best of luck!
Hi, can we create a code generator within VBE by inserting customised tool bar.
Hi Vijayalakshmi, do you mean something like MZ Tools? www.mztools.com/
@@WiseOwlTutorials can we create customised menu and sub menu in vbe menu bar which will generate codes automatically by clicking it ex: looping through sheets and copying data from one sheet to another sheet, looping through folders, creating sub procedure, functions with some logic in it. The main file which contains code will be in . Xlam format later can be shared with others. I am able to create customised menu in VBA but I have no idea how to generate codes from it.
@@vijayalakshmibadigeru4479 It's a complicated topic! Chip Pearson has the most detailed explanation of how to make the VB Editor write code into itself www.cpearson.com/excel/vbe.aspx
It's a long read but if you reach the end you will know as much as I do about this topic. I hope it helps!
@@WiseOwlTutorials sure, thank you.
Hi Andrew, Please refer www.automateexcel.com/
once. I was talking about this kind of customised VBE to create my own custom forms and functions. Can you please tell me how they have created using what tools VSTO or Dotnet.