Your videos should be translated in all the languages of the world and be used in all computer programming schools. You are simply the best! Thank you very much, God bless you.
This seems to be one of the shortest videos in this series so far, yet I have spent the most amount of time on it.I have found this so helpful in my worklife. I am going through all of these videos, just so I can understand arrays, but was so impressed by watching video 22 or 20something, I decided to start from the beginning so I could refresh everything I have learned on the fly over my years. I said in another post I had watched many other tutorials, some were very good, some were to boring for me to watch, but this guy, he is incredible. I hope I get a full understanding of what I need to know so I can do what I need to do.
I'm new to Excel VBA and macros, in general. Your video tutorials are excellent and have answered many of my questions as I learn this subject. I am most interested in User Forms and how they are put together. I have watched your Userform videos but am forcing myself to watch everything from Part 1 to the end so I don't miss any of the pertinent details of Excel VBA. Thanks so much for providing these videos!
Wow you have the greatest tutorial ever. I been writing VBA for a quite of time but after watching these videos I learned so much even from the basics. Thank you so much.
Thank you so much for all the effort you've put into teaching us VBA, for free. Genuinely appreciate it and one of the most useful things I've found on youtube to date
I've been re-watching these after doing so a few years ago and still learning new content. I had no idea you could save as with an optional FileType argument that lets you save as one of the dozens of file types - or that you even needed to do so for macro-enabled wkbks. Nor did I know that I could hit F1 on a method to open a help menu. There is no end to learning in Excel.
Thanks again for the video. Good to see how to open, save and save as. Fairly straight forward in Excel itself but a whole different matter in VBA - especially the xlsm filetype!
Thank you WiseOwl. Note that in 13:51 you can put directly the value 52 and it will work as well instead of copying and pasting :) Try it this way: Sub ChangingTheFileType() Workbooks.Add ActiveWorkbook.SaveAs "C:\Test workbook.xlsm", 52
Hi there, yes of course that's true, you can use the underlying value of any constant in VBA and it will still work. The problem is that it doesn't make the code very readable!
Andrew, thank you so much for your videos. My boss thinks i'm a genius! One thing I was hoping to get from this video was how to open a workbook that has a revolving file name by date. On a daily basis I have to copy and paste data from a workbook into my master workbook. The file name changes daily by the date (Workbook 4-26-16.xlsx to Workbook 4-27-16.xlsx). Is there a way to use VBA to make this process easier for me? Thank you again for your great videos.
Hi, Firstly, thank you for all the videos. They're really informative and easy to understand. I just saw the video on selecting workbooks. I was wondering if you have a video on selecting/activating workbooks that have a variable name. How would you refer to this workbook in your code if you don't know what the name is? Thanks.
WiseOwl, I am so grateful to have come across your tutorials, it really explained a whole lot of things that was giving me problems. i am working on a data base that will include First Name, Last Name, Address, Email, Phone Number, Date of Birth, Date of first Appointment, Date of Last Appointment, Date of Next Appointment, Current Spectacle prescription, Gender, Remarks. Will it be possible to do a tutorial on this for me, I am trying to do it for my Optometry clinic. - Ona Seriu-Aig
Thanks for this! I have a list of workbooks I want to extract data from. Is it possible to run through this list, and get the data from each file destination? For example, maybe put the list into a spreadsheet, and reference the range?
Thanks for all the videos of vba. They were quite helpful. The only problem that I am facing is how to add drop down menus within the vba code so that certain data can be selected according to requirement. It would be very kind of you if you could help me with this problem.
Thankyou for the video. I have applied this code in my programming and has saved time. One thing I wanted to ask when details are entered then the keyboard shortcuts need to be entered again to make the formulas work. Kindly help me towards this query. Thanks Mansi
I love your training videos - nice pace and clear instructions. I have just watched part 6 'Worksheets, Charts and Sheets' and have learned half of what I need to create. I need to copy the current worksheet to the beginning of the list of worksheets (learned that bit!) but WITHOUT any formulas being copied. How would I go about that?
***** Wow thanks for the quick reply - I actually only want to stop the sheet copying the formulas over though. So I need the formatting and the contents MINUS the formulas. Is this similar?
***** Andrew. Thanks again. I have just hit an obstacle. The original sheet is password protected so I have written the following code which is probably all wrong as it sticks after the password entry saying the password is not correct although I have checked it and I can unlock the sheet using the same password: Sub MoveCopyFigures() Worksheets("Front Sheet").Copy before:=Sheets(1) ActiveSheet.Unprotect Password = "nikwak1" Cells.Copy Cells.PasteSpecial xlPasteValues ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value ActiveSheet.Protect Password = "nikwak1" End Sub
***** Fantastic - HUGE thanks to you and I will now continue past video 7! This has always been a real grey area to me but you have made it really simple (so far lol) Nick
Great video (as all of them are). Maybe a good idea to also explain on how to reference a variable to other workbooks (to open and closed workbooks)? I do this already, but recently encoutered a problem with this referencing. Normally "Set wb = Workbooks(Workbookname whithout extension)" works well on open workbooks. But apperntly when you put "Make extensions of files visible" to true, this no longer works. I do not know why.
I'm having a little trouble. I could only create all the subs in the same Code Window, so I end up having to scroll down. Referring to 0:40 and 7:24, how do you program VBA so that a line automatically appears after typing a comment? How do you do that so that they are written as separate subroutines in new Code Windows without creating them as new individual modules in the Project Window?
i have written code to open a workbook whose reference is in a cell in an existing workbook and hace made this a variable....this works...however when i try to close the workbook by referring to the variable...it doesnt work and says subscript out of range.....any one know how to fix this?
I want to use sumifs in vba to sum a column or row that is in another workbook saved somewhere in the c drive. I managed to do it when THEY ARE IN THE SAME WORKBOOK. PLEASE ASSIST
Hey everyone, I'm on excel 2016. When I place the cursor anywhere on the SaveAs and click F1, instead of "Excel Developer Reference" (which looks like an amazingly useful tool), internet explorer opens a Microsoft docs page about visual studio 2012. Any idea why is it doing that? And how else can I access Developer reference? Thank you in advance!
Your tip for using the F1 key doesn't seem to work in the VBA editor for Excel 2016. It opens Microsoft Edge and then goes to the following URL: msdn.microsoft.com/en-us/library/dd831853(v=vs.110).aspx?appId=Dev11IDEF1&l=en-US&k=k(vbaxl10.chm199282)%3bk(TargetFrameworkMoniker-Office.Version%3dv16)&rd=true Which is not helpful. Is there some way around this or to get back to the behavior of earlier versions of Excel?
Creating new workbook based on Top Movies didn't work for me, it says something you can't open two workbooks with the same name, and why did you change file type to xltm from xlsm? i didn't follow there. change file type: Workbooks.Add ActiveWorkbook.SaveAs "c:\Test workbook.xlsm", xlOpenXMLWorkbookMacroEnabled can you tell what is wrong here? it didn't work for me.
Hi Andrew I'm trying to copy data from 3 workbooks(Amit.xls,Amit2.xlsx,Amit3.xlsx) into another workbook (Zmaster.xlsm) using do loop and dir method but getting an error mesage:- Sorry, we couldn't find amit.xlsx.Is it possible it was moved, renamed or deleted. However when I check for a value for myfile it is showing the name of the file from the folder and also the file is available in the folder. Pls help
hi After "openexistingworkbook" i am trying to add new workbook with template but new workbook is not creating. error appearing "run time error-1004, Sorry, we could not find location...... is it was moved,renamed or deleted?. What should I do?
Hi Andrew, First of all I thank you so much for these beautiful and wonderful series of VBA video. Please, could you help me with this dilemma. I have created couple of worksheet (like Invoice sheet, Contract sheet etc.) in a workbook for my customers. I have a folder name is : Customer 2014 whenever I write a contract or invoice for customer, I need that workbook to be saved in new sub-folder with the name of customer inside the folder "Customer 2014". I really appreciate if you can help me with code.
Yes, it is a bug! You can see some possible solutions here stackoverflow.com/questions/71154862/excel-workbooks-that-i-open-in-vba-still-appear-in-the-project-explorer-after-cl I hope it helps!
Hi Andrew, Thanks for your time doing these videos. I'm using the file name referring to a cell but if the filename already exist i would like to know the code how to refer to another cell or value. I have tried this IF but no luck. Thanking you again in advance. Roderick ChDir "C:\Users\Roderick\Documents\Order Form Tests" Dim newFn As Variant newFn = Worksheets("Quotation").Range("k8").Value & (" ") & Range("c6").Value If newFn.fileexcists("C:\Users\Roderick\Documents\Order Form Tests") Then Range("k8").Value +1 ActiveWorkbook.SaveAs newFn, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False End If
Yes, it's really annoying! Most keywords still work correctly with the F1 help but there are plenty of exceptions. I tend to just navigate to the root of the Excel VBA Object Model on Microsoft Docs and use the menu on the left or the search box to find the relevant item docs.microsoft.com/en-us/office/vba/api/overview/excel/object-model
Simply the best VBA video's I've found in using VBA over the last 20 years.
Your videos should be translated in all the languages of the world and be used in all computer programming schools. You are simply the best! Thank you very much, God bless you.
This seems to be one of the shortest videos in this series so far, yet I have spent the most amount of time on it.I have found this so helpful in my worklife. I am going through all of these videos, just so I can understand arrays, but was so impressed by watching video 22 or 20something, I decided to start from the beginning so I could refresh everything I have learned on the fly over my years. I said in another post I had watched many other tutorials, some were very good, some were to boring for me to watch, but this guy, he is incredible. I hope I get a full understanding of what I need to know so I can do what I need to do.
Thank you for the kind comments Jo! Very happy to hear that you've found the videos so useful and thank you for taking the time to let us know!
your video is a completely Bible of Excel VBA
- Thanks for the good and noble work
I'm new to Excel VBA and macros, in general. Your video tutorials are excellent and have answered many of my questions as I learn this subject. I am most interested in User Forms and how they are put together. I have watched your Userform videos but am forcing myself to watch everything from Part 1 to the end so I don't miss any of the pertinent details of Excel VBA. Thanks so much for providing these videos!
Wow you have the greatest tutorial ever. I been writing VBA for a quite of time but after watching these videos I learned so much even from the basics. Thank you so much.
Great!, your site is always a way to learn something new
Thanks Henrik!
Bonjour je suis entrain de revoir toutes vos vidéos. Je comprends beaucoup mieux. Merci et BRAVO encore. You are the best.
u r a great teacher!! love the way you teach, now i can uderstand VBA quiteeasily! thank you so much, will keep wathcing these series.
Thank you so much for all the effort you've put into teaching us VBA, for free. Genuinely appreciate it and one of the most useful things I've found on youtube to date
I've been re-watching these after doing so a few years ago and still learning new content. I had no idea you could save as with an optional FileType argument that lets you save as one of the dozens of file types - or that you even needed to do so for macro-enabled wkbks. Nor did I know that I could hit F1 on a method to open a help menu. There is no end to learning in Excel.
Thanks David, happy to hear that you're still finding value in the videos!
Thanks WiseOwl for another interesting Excel vba lesson. I will try to get through all your Excel vba lessons since I use Excel frequently at work.
Your videos are an awesome resource and these VBA tutorials have made my work life much better! Carry on good sir!!!
Great tutorials for slow learners like myself. well put, very detailed and the pace is perfect.
Thanks again for the video. Good to see how to open, save and save as. Fairly straight forward in Excel itself but a whole different matter in VBA - especially the xlsm filetype!
Your videos are the best available training videos for VBA. Thank you so much for that, i've learned a lot from them! :)
Awesome Videos on VBA coding for a complete novice, thanks for the upload
Excellent way of Teaching..!!!
Thanks, glad you liked it!
Thanks wise and kind owl. I love your videos. You are superb. May God bless you.
I really enjoy your tutorials. Well organized and well explained.
Thank you WiseOwl. Note that in 13:51 you can put directly the value 52 and it will work as well instead of copying and pasting :) Try it this way:
Sub ChangingTheFileType()
Workbooks.Add
ActiveWorkbook.SaveAs "C:\Test workbook.xlsm", 52
End Sub
Hi there, yes of course that's true, you can use the underlying value of any constant in VBA and it will still work. The problem is that it doesn't make the code very readable!
شكرا جزيلا على دروسك القيمة وعلى طريقة الشرح
You are a Master Teacher! Thanks for this great resource!
Awesome videos. I am really enjoying it.
Hi. I'm using Excel 2016 for these lessons. F1 does not take me to the context sensitive help system in Excel. Has the short cut key changed? Thanks
You are amazing, God bless you brother
Thank you!
Thank you so much. You are a great teacher!
Woohoo!!!! Excel vba is back....:)
Great thanks already have great ideas to use this tips , but one question , is there a code name for the worksheet like the sheet ?
These videos are the best instruction for VBA I have found on RUclips.
Will you be doing any for the Find and Findnext functions in VBA?
Andrew, thank you so much for your videos. My boss thinks i'm a genius! One thing I was hoping to get from this video was how to open a workbook that has a revolving file name by date. On a daily basis I have to copy and paste data from a workbook into my master workbook. The file name changes daily by the date (Workbook 4-26-16.xlsx to Workbook 4-27-16.xlsx). Is there a way to use VBA to make this process easier for me? Thank you again for your great videos.
Hi,
Firstly, thank you for all the videos. They're really informative and easy to understand. I just saw the video on selecting workbooks. I was wondering if you have a video on selecting/activating workbooks that have a variable name. How would you refer to this workbook in your code if you don't know what the name is? Thanks.
WiseOwl, I am so grateful to have come across your tutorials, it really explained a whole lot of things that was giving me problems.
i am working on a data base that will include First Name, Last Name, Address, Email, Phone Number, Date of Birth, Date of first Appointment, Date of Last Appointment, Date of Next Appointment, Current Spectacle prescription, Gender, Remarks.
Will it be possible to do a tutorial on this for me, I am trying to do it for my Optometry clinic. - Ona Seriu-Aig
Thanks for this! I have a list of workbooks I want to extract data from. Is it possible to run through this list, and get the data from each file destination? For example, maybe put the list into a spreadsheet, and reference the range?
Thanks for all the videos of vba. They were quite helpful. The only problem that I am facing is how to add drop down menus within the vba code so that certain data can be selected according to requirement. It would be very kind of you if you could help me with this problem.
Thankyou for the video. I have applied this code in my programming and has saved time. One thing I wanted to ask when details are entered then the keyboard shortcuts need to be entered again to make the formulas work. Kindly help me towards this query.
Thanks
Mansi
I love your training videos - nice pace and clear instructions. I have just watched part 6 'Worksheets, Charts and Sheets' and have learned half of what I need to create. I need to copy the current worksheet to the beginning of the list of worksheets (learned that bit!) but WITHOUT any formulas being copied. How would I go about that?
***** Wow thanks for the quick reply - I actually only want to stop the sheet copying the formulas over though. So I need the formatting and the contents MINUS the formulas. Is this similar?
***** Andrew. Thanks again. I have just hit an obstacle. The original sheet is password protected so I have written the following code which is probably all wrong as it sticks after the password entry saying the password is not correct although I have checked it and I can unlock the sheet using the same password:
Sub MoveCopyFigures()
Worksheets("Front Sheet").Copy before:=Sheets(1)
ActiveSheet.Unprotect Password = "nikwak1"
Cells.Copy
Cells.PasteSpecial xlPasteValues
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
ActiveSheet.Protect Password = "nikwak1"
End Sub
***** Fantastic - HUGE thanks to you and I will now continue past video 7! This has always been a real grey area to me but you have made it really simple (so far lol) Nick
Simply Awesome..!! Do you have any kind of code or functions glossary into pdf or any other format which can be downloaded?
Great video (as all of them are). Maybe a good idea to also explain on how to reference a variable to other workbooks (to open and closed workbooks)? I do this already, but recently encoutered a problem with this referencing. Normally "Set wb = Workbooks(Workbookname whithout extension)" works well on open workbooks. But apperntly when you put "Make extensions of files visible" to true, this no longer works. I do not know why.
You are Awesome!
Thanks Bryan!
Nice videos! Do you have any practice for us to apply the things that we have learned?
I'm having a little trouble. I could only create all the subs in the same Code Window, so I end up having to scroll down.
Referring to 0:40 and 7:24, how do you program VBA so that a line automatically appears after typing a comment?
How do you do that so that they are written as separate subroutines in new Code Windows without creating them as new individual modules in the Project Window?
Excellent Videos!
i have written code to open a workbook whose reference is in a cell in an existing workbook and hace made this a variable....this works...however when i try to close the workbook by referring to the variable...it doesnt work and says subscript out of range.....any one know how to fix this?
I want to use sumifs in vba to sum a column or row that is in another workbook saved somewhere in the c drive. I managed to do it when THEY ARE IN THE SAME WORKBOOK. PLEASE ASSIST
in my experience and in my case (using office 2013)you don't have to add the extension xlsx or xlsm for workbooks. thank you.
Hey everyone,
I'm on excel 2016. When I place the cursor anywhere on the SaveAs and click F1, instead of "Excel Developer Reference" (which looks like an amazingly useful tool), internet explorer opens a Microsoft docs page about visual studio 2012. Any idea why is it doing that? And how else can I access Developer reference?
Thank you in advance!
Because the new office package doesn't contain a integrated manual anymore. The internet one is fine.
Your tip for using the F1 key doesn't seem to work in the VBA editor for Excel 2016. It opens Microsoft Edge and then goes to the following URL: msdn.microsoft.com/en-us/library/dd831853(v=vs.110).aspx?appId=Dev11IDEF1&l=en-US&k=k(vbaxl10.chm199282)%3bk(TargetFrameworkMoniker-Office.Version%3dv16)&rd=true
Which is not helpful. Is there some way around this or to get back to the behavior of earlier versions of Excel?
Thanks.
Many thanks to you
Creating new workbook based on Top Movies didn't work for me, it says something you can't open two workbooks with the same name, and why did you change file type to xltm from xlsm? i didn't follow there.
change file type:
Workbooks.Add
ActiveWorkbook.SaveAs "c:\Test workbook.xlsm", xlOpenXMLWorkbookMacroEnabled
can you tell what is wrong here? it didn't work for me.
Thank you for video!
Hi Andrew
I'm trying to copy data from 3 workbooks(Amit.xls,Amit2.xlsx,Amit3.xlsx) into another workbook (Zmaster.xlsm) using do loop and dir method but getting an error mesage:-
Sorry, we couldn't find amit.xlsx.Is it possible it was moved, renamed or deleted.
However when I check for a value for
myfile it is showing the name of the file from the folder and also the file is available in the folder.
Pls help
Many thanks
How do I get to the VBA documentation help? I'm using Excel 2021
Hi! You can go here learn.microsoft.com/en-us/office/vba/api/overview/excel
I hope it helps!
In excel 2013 i didnt have to copy the code to change the file type it could save directly by specifying xlsm. :)
Thank you!
12:09 Why doesn't it save as "C:\Test workbook.xlsx"? You put the C:\ in quotes also.
hi
After "openexistingworkbook" i am trying to add new workbook with template but new workbook is not creating. error appearing "run time error-1004, Sorry, we could not find location...... is it was moved,renamed or deleted?.
What should I do?
I am finding that worbooks I save as templates are saved as xltx rather than xltm - thank you for your videos by the way they are very clear
thanks!
super cool
Hi Andrew, First of all I thank you so much for these beautiful and wonderful series of VBA video. Please, could you help me with this dilemma.
I have created couple of worksheet (like Invoice sheet, Contract sheet etc.) in a workbook for my customers.
I have a folder name is : Customer 2014
whenever I write a contract or invoice for customer, I need that workbook to be saved in new sub-folder with the name of customer inside the folder "Customer 2014".
I really appreciate if you can help me with code.
Thanks
What happens with this? If you close a workbook, it remains open in Project Explorer. Is this a bug?
Yes, it is a bug! You can see some possible solutions here stackoverflow.com/questions/71154862/excel-workbooks-that-i-open-in-vba-still-appear-in-the-project-explorer-after-cl
I hope it helps!
Awesome!
hi Andrew,
i need these modules,
how can i download
ho.... ok
if possible please send to my mail id
really i loved this
Hi Andrew,
Thanks for your time doing these videos.
I'm using the file name referring to a cell but if the filename already exist i would like to know the code how to refer to another cell or value.
I have tried this IF but no luck.
Thanking you again in advance.
Roderick
ChDir "C:\Users\Roderick\Documents\Order Form Tests"
Dim newFn As Variant
newFn = Worksheets("Quotation").Range("k8").Value & (" ") & Range("c6").Value
If newFn.fileexcists("C:\Users\Roderick\Documents\Order Form Tests") Then
Range("k8").Value +1
ActiveWorkbook.SaveAs newFn, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End If
***** Thanks for your prompt reply.
I'll give it a try.
Roderick
How can the workbook keyword appear in the line code? I couldn't do like you did.
Ctrl + spacebar
And then they made the help function link to Visual Studio, online. terrible
Yes, it's really annoying! Most keywords still work correctly with the F1 help but there are plenty of exceptions. I tend to just navigate to the root of the Excel VBA Object Model on Microsoft Docs and use the menu on the left or the search box to find the relevant item docs.microsoft.com/en-us/office/vba/api/overview/excel/object-model
@@WiseOwlTutorials Many thanks for the quick answer and these great videos!
@@WalyB01 No worries, thanks for the support!