I've been doing VBA for the office for a while, and I have still learned some invaluable lessons from these videos. You can feel the genuine excitement through all the tips and tricks, and the professionalism of the presentation surpasses most class rooms - that's some damn good VBA!
I search tutorial, and in trial and error I have found that yours are the best. I no longer waist time watch other ones. I've watch and take notes on about a dozen of your videos. It's allow me to improve process and work. I now have code to do just about every thing I need to do, even opening up a text file. LOL... But I open several at a time. YOU ARE THE BEST, THANK YOU. I just bought your book Introduction to Excel VBA.
It's the world's best online training institute. Andrew, Almighty has made you very talented. Please keep on sharing the knowledge. May Almighty bless you.
Andrew I got a new analyst job just because I manipulated the interview only after watching few of your videos and later on I learn before I actually started the job lol. Anyways lot of analysts actually suggest watching you. Cheers Mate!!
Although this is a few years ago now lol, I have found this to be the best to learn from.Lots of things are repeated over and over again, which is great because it is not assumed you know what you are doing. I have watched 3 other tutors, watching so many of their videos, but at last I have found this. very happy
Hi Jo, these videos are getting a bit old now, you're right! We recently updated the earlier videos in this series and created a separate online tutorial covering the basics of VBA www.wiseowl.co.uk/online-training/excel-vba/ The tutorial covers everything up to around Part 19-20 of the older series and has downloadable files and exercises. Just in case you were looking for something a little more up to date!
WiseOwl, this instructor is good. He has left nothing untouched, very thorough and direct. Thank you so much for sharing the knowledge. You are blessed.
I'm working as a mechanical engineer and use Excel as my calculation and reporting tool along with Word so as you say VBA is will enhance my productivity, that's for sure. I see that one has to be careful with worksheet names ( between what the user sees and what is ). I'm going to contribute to your cause for sure. I'm learning Python as well
I'm only on video 6/77 but I feel like I've already learned so much from watching this. Kudos to the instructor for making the lesson so easy to understand.
I immensely appreciate your content delivery. As a presenter, you have a beautiful voice which makes learning enormously motivating as well as inviting. Thanks for putting in so much work in these videos; one also learns about presentation skills thorough your videos. I am from the UK, and I really hope to see you one day. Thanks, Vijay
Your lessons are extremly usefull for learning VBA. I am Ukrainian, actualy i don't understand completly your language, but i understand code language VBA. Thanks for good lessons, the first one is VBA and the second one is English.
excellent teaching! I started with no knowledge of VBA, but your teaching is very clear ! I have learnt a lot, thank you very much! After VBA, I will watch your SQL vedios.
Fantastic course. Not only for beginners - I have been using VBA for long time by now, but I have learned new tricks and generally the course let me a kind of declutter my knowledge. Thanks a million.
Thank you for this video and your great lessons. As many other people already mentioned, you're extremely clear and the way you explain VBA is very easy to follow.
Thank you so much, Wise Owl, Before I was afraid of VBA until that I ask ChatGPT, How can I learn VBA, and it gave me your TH-cam channel, Since this moment, I feel good with VBA and I learn more every day.
WiseOwl thanks for the effort you put into your work! Really appreciate your tutorials and it is not only useful but even fun to watch! Only the best to you!
14:42 If you are unable to change the code name because you are getting the message "invalid property value" follow these instructions. In your workbook, go to Options -> Trust Center ->Trust Center Settings and check the box for "Trust access to the VBA project object model"
Sir. thank you are so brilliant, the way you teach is very easy to learn keep up the wonderful job. God bless you and congratulatios. You have a new friend in Colombia.
In the beginning of the lesson, where did the first chart come from? How did you create it? Regarding Choosing Where to Insert Worksheets, the tab names of the three new sheets appear in descending order. Without using VB Editor, how do I program Excel so they appear in ascending order? And how do I program it so that the new worksheet tab in 16:50 reads Sheet1 instead of Sheet5?
Dear Andrew, I never found a teacher clear, simple, practical, competent and that made me passionate about a subject like you. I no longer really know how to thank you for the joy with which I watch your videos, which make me learn something more each time .. You are really fantastic! A question please: is there a way when VBA goes wrong, instead of pointing out in yellow the whole line of code, to highlight the single part of the line where there is the error? Thanks again. Ciao!
Hello WiseOwl, At 24:38 Code Sheets.Add Type:=XlSheetType.xlChart adds chart at the left of the active sheet. How can we use this code to add the chart at the right of the active sheet? Thanks in advance!
+WiseOwlTutorials Hi! So, currently my worksheets are in following order: Sheet1, Sheet2, Chart1 and Sheet3. Now, if I run the code "Sheets.Add after:=Sheets(4), Type:=XlSheetType.xlChart" it adds a new chart immediately after Chart2 and not after Sheet3. I want to add a chart after all the currently present sheets. Request your help. Thanks!
+WiseOwlTutorials This doesn't work either! What I have realized is the code "Sheets.Add after:=Sheets(4), Type:=XlSheetType.xlChart" works fine for index numbers 1,2&3. The problem is only when we are referring to the last sheet in the workbook. As a experiment, I added a new worksheet Sheet4 after Sheet3 and now the code runs fine till index no. 1,2,3&4 but not for number 5. Wierd but none of the codes are allowing us to add a chart at the very end (rightmost place).
Great videos, no doubt the best in TH-cam for learning VBA! Apparently, you can change CodeNames programatically, for instance: Sub change_code_name() Dim wbk As Object, sheet As Object ActiveWorkbook.VBProject.Name = "VBAProject" Set wbk = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.CodeName) wbk.Name = "wbk_code_name" Set sheet = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.Sheets(1).CodeName) sheet.Name = "sheet_code_name" End Sub (access to the VBAProject is required, check macro settings).
Wise Owl, great VBA videos after watching your videos along with my VBA book I feel I'm in pretty good shape. In your VBA video #6 you say you are going to do a video with how to save and close documents, I was wondering will that video also explain Dim for variables? Thanks for your videos looking forward to the new ones.
Help How do you save the selected sheet or chart do stuff on other sheets then redisplay the sheet or chart. for sheets I save the current activesheets and use workshhes(Currenetsheet).activate but get error when a chart sheet is selected
I really love your tutorials! The other 5 so far I have been able to work alongside you and do what you're doing to learn from doing it myself, but with this one, I couldn't really do that because I didn't have the base excel file. That is literally my only complaint so far! Considering this video is almost five years old though, I don't see you adding a download link in your description! Haha
Just coming back to using Excel and VBA after several years and away and agree with you on this one. Not sure why manipulating Charts is here when no mention of how to create them in the first place. Had to do some study elsewhere on how to create a Chart just so I could follow along with this tutorial.
Hi, thank you very much for this brilliant video, which helps a lot. However, here i got a little confused with the intellisense, when i wrote [ worksheets("sheet1"). ] it didn't show up and after i typed copy by myself and hit space once the arguments for before or after didn't show up either. so should it be like this? Look forward to hearing from you.
Great video! Is there a way to close a worksheet before the workbook is closed? I have two macros that hide and unhide a worksheet, but I'm having trouble finding a way to have it automatically close when the entire workbook is closed. Not sure if that makes sense or not. Thanks in advance!
I'm on Office 2016 and this is all very relevant however not sure that the distinction between "charts" and "worksheets" are relevant as seems you can combine one into the other
Hi WiseOwl, after thanking you for your great efforts, I have learned a lot from your videos! Just a quick notification, in the above video "Excel VBA Introduction Part 6 - Worksheets, Charts and Sheets", at min 32, video stops loading or working and it freezes for ages, any idea please about this issue, or can you re-upload it? Thanks, Huda
Very very thanks Wiseowl for these videos. I have one doubt from this session (Part-6). How can you delete multiple sheets, let's say: to delete 3 selected worksheets in same workbook. Is there a single command to execute this in one go.
Hai Andrew, thank you for yours tutorials. I want to rename my sheet name as Todays date, upon repeating the process the date as to pop up automatically in the new sheet name.can you please help with macro
Hi Andrew, Thanks for your wonderful tutorials. Today I am bamboozled by a simple code. Worksheets(paEXtract).Activate, generates a runtime error 13. I am not sure what is wrong. Could you pleaser help? Thanks
Hi ! Amazing videos! One question: Is there anywhere I can download/see all the codes written during the videos (just in txt. format e.g.), so I don't have to go through the video again to see the syntax of something specific?
Really Comprehensive and easy to understand. But is there any fast way to remember these functions? or I guess I just need to have more practices to get acquaint and familiarized with these functions :)
Hi Andrew great tutorial. one question comes up, Is it possible to change visible property to veryhidden and than disable for other users to change this property ?
Hi Andrew, thank you for the lovely tutorial. Old is gold so the this and other tutorials in this series never get old. I have a short question. I wrote a code which is copying specific data from one workbook (only one sheet) and copies it to a new workbook in different sheets depending upon some criteria. The code works perfectly fine till it inserts fifth sheet (data copying works fine for fifth sheet). Only after that when I expect the code to insert another sheet (sheet 6), I get "Run-time error '9', Subscript out of range". I am copying the section of the code which inserts new sheet in the Output_workbook.xlsm. ' here j takes the value of sheets count when the code starts and stays in the same index number j = Workbooks("Output_workbook.xlsm").Sheets.Count Workbooks("source_workbook.xlsm").Worksheets("KgKw").Activate If Range("A2").Offset(i, 2).Value Range("A2").Offset(i - 1, 2).Value And Range("A2").Offset(i, 2).Value "" Then ' i is the data counter in the source_workbook.xlsm
Workbooks("Output_workbook.xlsm").Sheets(j).Range("A:D").EntireColumn.AutoFit Workbooks("Output_workbook.xlsm").Sheets.Add after:=Sheets(j) ' after inserting the new sheet, value of j is being updated j = Workbooks("Output_workbook.xlsm").Sheets.Count
End If Could you give any hints as why the code fails after inserting five sheets? Thank you, Zubair
Hi Zubair! You've been very careful to explicitly reference the sheets collection in the respective workbooks except in one place: Workbooks("Output_workbook.xlsm").Sheets.Add after:=Sheets(j) Try this: Workbooks("Output_workbook.xlsm").Sheets.Add after:=Workbooks("Output_workbook.xlsm").Sheets(j) I hope it helps!
Addressing referring the sheets by their code name, why can't I do this: ThisWorkbook.Sheet1.Activate but I can run Sheet1.Activate? One more thing please, are there conditions in the used code name? I'm trying to rename a sheet code name to be Rooster but it doesn't accept, invalid property.
Hi Magdoulin! The short answer to the first question is that sheet codenames are not defined as members of the object referred to by ThisWorkbook. Sheet codenames are members of the VBA project that your module belongs to so, assuming that you haven't changed the project name, you can do this: VBAProject.Sheet1.Activate There certainly are naming rules for things in VBA docs.microsoft.com/en-us/office/vba/language/concepts/getting-started/visual-basic-naming-rules I wasn't aware that Rooster was a reserved word! Do you have anything else with that name in your VBA project?
35:42 You can't write code to change the codename? I am sure Andrew can do anything, maybe if you trust access to the VBA project model. Your tutorials are just amazing. Let me say that again. Your tutorials are just amazing, even my dog thinks so.
Rodrigo polan My mistake Andrew, I apologize. I found something called "Plot Digitizer" then I can use the points in excel. But if you know something else I would like to know. Thanks, Rodrigo
Best channel to learn VBA. I learnt from this a lot.
Thanks so much, I appreciate the comments!
My company paid like $1000 dollars for me to take a VBA course that was half as useful as these videos. You are doing a wonderful thing posting these.
I've been doing VBA for the office for a while, and I have still learned some invaluable lessons from these videos. You can feel the genuine excitement through all the tips and tricks, and the professionalism of the presentation surpasses most class rooms - that's some damn good VBA!
I search tutorial, and in trial and error I have found that yours are the best. I no longer waist time watch other ones. I've watch and take notes on about a dozen of your videos. It's allow me to improve process and work. I now have code to do just about every thing I need to do, even opening up a text file. LOL... But I open several at a time. YOU ARE THE BEST, THANK YOU. I just bought your book Introduction to Excel VBA.
In my job VBA is used everyday and I had no zero experience prior so thanks to you I'm not fired :)
It's the world's best online training institute.
Andrew, Almighty has made you very talented.
Please keep on sharing the knowledge.
May Almighty bless you.
Andrew I got a new analyst job just because I manipulated the interview only after watching few of your videos and later on I learn before I actually started the job lol. Anyways lot of analysts actually suggest watching you. Cheers Mate!!
Although this is a few years ago now lol, I have found this to be the best to learn from.Lots of things are repeated over and over again, which is great because it is not assumed you know what you are doing. I have watched 3 other tutors, watching so many of their videos, but at last I have found this. very happy
Hi Jo, these videos are getting a bit old now, you're right! We recently updated the earlier videos in this series and created a separate online tutorial covering the basics of VBA www.wiseowl.co.uk/online-training/excel-vba/
The tutorial covers everything up to around Part 19-20 of the older series and has downloadable files and exercises.
Just in case you were looking for something a little more up to date!
WiseOwl, this instructor is good. He has left nothing untouched, very thorough and direct.
Thank you so much for sharing the knowledge. You are blessed.
Thanks to the best VBA teacher worldwide. I have really watched many tutorials. Your ones are undoubtedly the best.
🌟🌟🌟 🌟 🌟 🌟 🌟 🌟 🌟 🌟
You've got talent for teaching ! I've been playing with Excel for years, but never VBA. I see the power now ! You can do anything and much faster !
I'm working as a mechanical engineer and use Excel as my calculation and reporting tool along with Word so as you say VBA is will enhance my productivity, that's for sure. I see that one has to be careful with worksheet names ( between what the user sees and what is ). I'm going to contribute to your cause for sure. I'm learning Python as well
I'm only on video 6/77 but I feel like I've already learned so much from watching this. Kudos to the instructor for making the lesson so easy to understand.
Same
I immensely appreciate your content delivery. As a presenter, you have a beautiful voice which makes learning enormously motivating as well as inviting. Thanks for putting in so much work in these videos; one also learns about presentation skills thorough your videos. I am from the UK, and I really hope to see you one day. Thanks, Vijay
I don't know what to say except that it can't be better than this.
May God bless the Very amazing Tutor.
Tanks of thanks.
Your lessons are extremly usefull for learning VBA.
I am Ukrainian, actualy i don't understand completly your language, but i understand code language VBA. Thanks for good lessons, the first one is VBA and the second one is English.
Simply fantastic! Clear, simple, gradual, essential. Thanks endless!
excellent teaching! I started with no knowledge of VBA, but your teaching is very clear ! I have learnt a lot, thank you very much! After VBA, I will watch your SQL vedios.
Fantastic course. Not only for beginners - I have been using VBA for long time by now, but I have learned new tricks and generally the course let me a kind of declutter my knowledge. Thanks a million.
Hello you all.... SIMPLY one of best channels i have seen so far that covers the Excel VBA, Thank you .
Thank you for this video and your great lessons. As many other people already mentioned, you're extremely clear and the way you explain VBA is very easy to follow.
All your VBA Videos are very resourceful ... i must say you have tried to teach minute details in your videos... thanks a lot
Your tutoriels are so clear and ease to follow! Thank you so much for doing that for us Andrew!
Your teaching style is excellent, thank you for taking the time to put these together!
My favorite youtube tutorials vba and sql. You are awesome, Andrew!
Excellent lesson, simply and concisely delivered. Thank you for sharing your MS Excel knowledge with your viewers.
Thank you so much for these tutorials! So easy to follow and helpful - should have discovered these videos years ago !!!
Thanks Gerald! Happy to hear that you're finding them useful!
Thank you so much, Wise Owl, Before I was afraid of VBA until that I ask ChatGPT, How can I learn VBA, and it gave me your TH-cam channel, Since this moment, I feel good with VBA and I learn more every day.
I'm really happy to hear that you're finding the VBA videos useful! Thanks for taking the time to leave a comment and for watching!
honestly your videos are amazing. i never expected myself to learn as quick. thank you very much!
WiseOwl thanks for the effort you put into your work! Really appreciate your tutorials and it is not only useful but even fun to watch! Only the best to you!
Thank you Aleksandr! I'm happy that you're enjoying the videos and thank you for taking the time to leave a comment!
Andrew, you are simply class.
14:42 If you are unable to change the code name because you are getting the message "invalid property value" follow these instructions. In your workbook, go to Options -> Trust Center ->Trust Center Settings and check the box for "Trust access to the VBA project object model"
I would just like to thank you for your awesome tutorials! You Sir, have some great teaching skills and voice :D
Sir. thank you are so brilliant, the way you teach is very easy to learn
keep up the wonderful job. God bless you and congratulatios.
You have a new friend in Colombia.
Thanks for your video, I learn your video every evening. You really help me a lot!
WiseOwl who is this instructor? He is brilliant! I have learned so much!
Haha that response truly made my day!
I SO AGREE! hes absolutely amazing. So precise and thorough, and usable examples that freaking WOrk!!! tytytytytytytyty
Not Batman but New version: Owlman
The way you teach is awesome! Great work!
Bought a book! Yay (goosebumps!!) can’t wait to study it! Will check the website for everything else for sure! Thank you!!;)
it's amazing... I'm loving it.. the way trainer explain, that is awesome..😊
Going through your wonderful tutorials... well paced with useful contents. Thanks for sharing the knowledge and time.
thank you sir. Very useful as usual ..keep it up the good work please for us. I am fan of your teaching style and knowledge.
I loved the refering to sheets by code method!
Andrew,
Brilliant Teaching. I enjoy your tutorials very much.
Super teaching technique, very pleasing voice. Thank you.
Thank you for an interesting video. I've been writing macros for some time, but the part related to using worksheets' codename property was new to me.
In the beginning of the lesson, where did the first chart come from? How did you create it?
Regarding Choosing Where to Insert Worksheets, the tab names of the three new sheets appear in descending order. Without using VB Editor, how do I program Excel so they appear in ascending order?
And how do I program it so that the new worksheet tab in 16:50 reads Sheet1 instead of Sheet5?
excellent videoes on VBA. Thank you for your valuable time for making and sharing such nice videoes..!!!
I find this very helpful and thank you for sharing the knowledge.
Dear Andrew, I never found a teacher clear, simple, practical, competent and that made me passionate about a subject like you. I no longer really know how to thank you for the joy with which I watch your videos, which make me learn something more each time .. You are really fantastic!
A question please: is there a way when VBA goes wrong, instead of pointing out in yellow the whole line of code, to highlight the single part of the line where there is the error?
Thanks again. Ciao!
You are Amazing.
Hope you have a wonderful life.
I love you man!! The best tutorial ever! If you have got any book written for coding or any other excel tips please let me know!!;)
This is AMAZING. Genuinely thank you so much
Yes, it's a briliant tutorial. Thanks so much!
best tutorial ever....head-chop guaranteed
Awesome once Again! 🙏
Thanks again for watching!
This tutor is awesome
Hello WiseOwl,
At 24:38
Code Sheets.Add Type:=XlSheetType.xlChart adds chart at the left of the active sheet.
How can we use this code to add the chart at the right of the active sheet?
Thanks in advance!
+WiseOwlTutorials
Hi!
So, currently my worksheets are in following order: Sheet1, Sheet2, Chart1 and Sheet3.
Now, if I run the code "Sheets.Add after:=Sheets(4), Type:=XlSheetType.xlChart" it adds a new chart immediately after Chart2 and not after Sheet3.
I want to add a chart after all the currently present sheets.
Request your help.
Thanks!
+WiseOwlTutorials
This doesn't work either!
What I have realized is the code "Sheets.Add after:=Sheets(4), Type:=XlSheetType.xlChart" works fine for index numbers 1,2&3. The problem is only when we are referring to the last sheet in the workbook.
As a experiment, I added a new worksheet Sheet4 after Sheet3 and now the code runs fine till index no. 1,2,3&4 but not for number 5.
Wierd but none of the codes are allowing us to add a chart at the very end (rightmost place).
+WiseOwlTutorials
Yes. This now works!
Thanks!
Great videos, no doubt the best in TH-cam for learning VBA!
Apparently, you can change CodeNames programatically, for instance:
Sub change_code_name()
Dim wbk As Object, sheet As Object
ActiveWorkbook.VBProject.Name = "VBAProject"
Set wbk = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.CodeName)
wbk.Name = "wbk_code_name"
Set sheet = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.Sheets(1).CodeName)
sheet.Name = "sheet_code_name"
End Sub
(access to the VBAProject is required, check macro settings).
Thanks for sharing! Indeed you can!
thank you Andrew....
sharing the wisdom
The code you showed for selecting multiple worksheets at around 8:00 doesn't work. I am using Excel 2013.
Same here, got office 2016
Great videos. Learned so much. Do you have a video on how to select all sheets in a workbook and format all cells in a range?
Wise Owl, great VBA videos after watching your videos along with my VBA book I feel I'm in pretty good shape.
In your VBA video #6 you say you are going to do a video with how to save and close documents, I was wondering will that video also explain Dim for variables? Thanks for your videos looking forward to the new ones.
I certainly did find that useful. Thanks!
Help
How do you save the selected sheet or chart do stuff on other sheets then redisplay the sheet or chart. for sheets I save the current activesheets and use workshhes(Currenetsheet).activate but get error when a chart sheet is selected
Great work! Thank you!
Thanks Noel!
I really love your tutorials! The other 5 so far I have been able to work alongside you and do what you're doing to learn from doing it myself, but with this one, I couldn't really do that because I didn't have the base excel file. That is literally my only complaint so far! Considering this video is almost five years old though, I don't see you adding a download link in your description! Haha
Just coming back to using Excel and VBA after several years and away and agree with you on this one. Not sure why manipulating Charts is here when no mention of how to create them in the first place. Had to do some study elsewhere on how to create a Chart just so I could follow along with this tutorial.
I dont know why , but is am getting error called "object required", can you please help
Hi, thank you very much for this brilliant video, which helps a lot. However, here i got a little confused with the intellisense, when i wrote [ worksheets("sheet1"). ] it didn't show up and after i typed copy by myself and hit space once the arguments for before or after didn't show up either. so should it be like this? Look forward to hearing from you.
Great video! Is there a way to close a worksheet before the workbook is closed? I have two macros that hide and unhide a worksheet, but I'm having trouble finding a way to have it automatically close when the entire workbook is closed. Not sure if that makes sense or not. Thanks in advance!
Thank You! I can not afford to pay for these instructions. In this instance the old maxim " you get what you pay for" does not apply.
ty very much , and how can we change the CodeName of a newsheet?
Another great tutorial!
I'm on Office 2016 and this is all very relevant however not sure that the distinction between "charts" and "worksheets" are relevant as seems you can combine one into the other
Hi, If I wanted to select 3 worksheets, where would the false statement be placed? After the third, or on the second and third?
Hi WiseOwl, after thanking you for your great efforts, I have learned a lot from your videos!
Just a quick notification, in the above video "Excel VBA Introduction Part 6 - Worksheets, Charts and Sheets", at min 32, video stops loading or working and it freezes for ages, any idea please about this issue, or can you re-upload it?
Thanks,
Huda
Very very thanks Wiseowl for these videos.
I have one doubt from this session (Part-6). How can you delete multiple sheets, let's say: to delete 3 selected worksheets in same workbook. Is there a single command to execute this in one go.
where to get your working excel sheets for practice for every videos
?can i delete worksheets by referencing to the active one, if so can you teach me how
Hai Andrew, thank you for yours tutorials. I want to rename my sheet name as Todays date, upon repeating the process the date as to pop up automatically in the new sheet name.can you please help with macro
Hi Andrew,
Thanks for your wonderful tutorials. Today I am bamboozled by a simple code. Worksheets(paEXtract).Activate, generates a runtime error 13. I am not sure what is wrong. Could you pleaser help?
Thanks
Can you increase video size (not length) or resolution. I have to go full 1080p to view characters/text. And my computer slows down at that resolution
Hi ! Amazing videos!
One question: Is there anywhere I can download/see all the codes written during the videos (just in txt. format e.g.), so I don't have to go through the video again to see the syntax of something specific?
This guy is awesome!
So nice tutor. Thank you so much
What is the main difference between using Worksheets and Sheets in VBA code? Are they both same or is there any purpose in using specific keyword?
When did he create the Chart? which video or tutorial
most amazing vba
Really Comprehensive and easy to understand. But is there any fast way to remember these functions? or I guess I just need to have more practices to get acquaint and familiarized with these functions :)
Hi Andrew
great tutorial. one question comes up, Is it possible to change visible property to veryhidden and than disable for other users to change this property ?
***** yes ,definitely helps. I keep watching your videos !
Hi Andrew, do you have sample data that you used in this video available on your website?
In Excel for offlice 365 (MVBA 7.1) it seems that you have to specify ActiveWorkbook.Sheets.Add .... Just using Sheets.add will not work.
Hi Andrew, thank you for the lovely tutorial. Old is gold so the this and other tutorials in this series never get old.
I have a short question. I wrote a code which is copying specific data from one workbook (only one sheet) and copies it to a new workbook in different sheets depending upon some criteria. The code works perfectly fine till it inserts fifth sheet (data copying works fine for fifth sheet). Only after that when I expect the code to insert another sheet (sheet 6), I get "Run-time error '9', Subscript out of range". I am copying the section of the code which inserts new sheet in the Output_workbook.xlsm.
' here j takes the value of sheets count when the code starts and stays in the same index number
j = Workbooks("Output_workbook.xlsm").Sheets.Count
Workbooks("source_workbook.xlsm").Worksheets("KgKw").Activate
If Range("A2").Offset(i, 2).Value Range("A2").Offset(i - 1, 2).Value And Range("A2").Offset(i, 2).Value "" Then ' i is the data counter in the source_workbook.xlsm
Workbooks("Output_workbook.xlsm").Sheets(j).Range("A:D").EntireColumn.AutoFit
Workbooks("Output_workbook.xlsm").Sheets.Add after:=Sheets(j)
' after inserting the new sheet, value of j is being updated
j = Workbooks("Output_workbook.xlsm").Sheets.Count
End If
Could you give any hints as why the code fails after inserting five sheets?
Thank you,
Zubair
Hi Zubair! You've been very careful to explicitly reference the sheets collection in the respective workbooks except in one place:
Workbooks("Output_workbook.xlsm").Sheets.Add after:=Sheets(j)
Try this:
Workbooks("Output_workbook.xlsm").Sheets.Add after:=Workbooks("Output_workbook.xlsm").Sheets(j)
I hope it helps!
@@WiseOwlTutorials thank you for the hint. I actually figured it out and now the code is completely error free.
@@zubairkahn1983 Happy to hear you found a solution!
How can multiple sheets be selected by tab color?
Great teaching. When new vba tutorial?
Thank You! Awesome Tutorial
Hi...where can i find a excel vba video for pivot tables..
how does he have the add function i dont have it in my excel2013
very good explained, Thanks a lot
Addressing referring the sheets by their code name, why can't I do this: ThisWorkbook.Sheet1.Activate but I can run Sheet1.Activate?
One more thing please, are there conditions in the used code name? I'm trying to rename a sheet code name to be Rooster but it doesn't accept, invalid property.
Hi Magdoulin! The short answer to the first question is that sheet codenames are not defined as members of the object referred to by ThisWorkbook. Sheet codenames are members of the VBA project that your module belongs to so, assuming that you haven't changed the project name, you can do this:
VBAProject.Sheet1.Activate
There certainly are naming rules for things in VBA docs.microsoft.com/en-us/office/vba/language/concepts/getting-started/visual-basic-naming-rules
I wasn't aware that Rooster was a reserved word! Do you have anything else with that name in your VBA project?
35:42 You can't write code to change the codename? I am sure Andrew can do anything, maybe if you trust access to the VBA project model.
Your tutorials are just amazing. Let me say that again. Your tutorials are just amazing, even my dog thinks so.
try ActiveWorkbook.VBProject.VBComponents("Sheet1").Name = "NewCodeName" should do it. :)
really awesome, more plentiful than is usual or necessary.
Hello Andrew,
Do you know how can I find a equation or trendline from a picture of a curve in excel VBA or excel ?
Thank you,
Rodrigo
Rodrigo polan
My mistake Andrew, I apologize.
I found something called "Plot Digitizer" then I can use the points in excel. But if you know something else I would like to know.
Thanks,
Rodrigo