Excel VBA Basics #13 Switching Between Workbooks Dynamically, Create New Workbooks

In this lesson, we learn how to teach our macro which sheet to select and when using the Thisworkbook.Activate AND Workbooks object to select a different sheet (eg Workbooks("Book2").Activate ).

We also show you how to make a new workbook on the fly and how to grab the new workbook's name so no matter what it is you can switch back and forth! Check it out!

Fantastic Developer Tools:

[JanBolhuis]

Thanks for reply Daniel. What I mean is when I start a function in Excel, e.g. VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), I see the arguments after typing =VLOOKUP(. Another (maybe better) examples are:

SUMIF(range, criteria, [sum_range]) where you first have to select the range for the criteria and by using
SUMIFS(sum_range, criteria_range1, criteria1, ...) you first need to select the range for your sum.

A lot of Excel users aren't aware of these very helpfull tips

[andrewasher]

Thank you! I'm here in 2019 and I have not found much on this topic until I found your video.

[ExcelVbaIsFun]

Merry Christmas everyone. I hope God blesses you richly this upcoming year. Remember, you ARE a success!! Believe it, be it! Blessings my friends!! Dan

[AlexFariaOliveira]

One million times thank you! This seems simple but it helped me a lot in my current project!

[sarveshrajwani]

Brother thanks a ton 🙃🙃🙃🙃
Small small problem troubles a lot and you helped in solving that

[blueshade]

Thanks for the excellent videos. I watched the one about about if-then-else statements while half asleep and the next day I was able to write my first working script! It was like it just came to me in a dream lol

[ginger]

WHAT A GREAT VIDEO. Like what you said, this is really good stuff!

[jkjersey]

Thank you for these videos, great resource.

[ExcelVbaIsFun]

I believe if you have option explicit, you would then have to declare all variables up front. So any objects being set or variables would need to be declared before use. Dim blah as Variant

Dim myNumber as Double

etc. . .

[AadilAhmadme]

Thanks a lot. It really helped me out.

[JanBolhuis]

Another great explanation. Thanks.

Is there a trick to see also the screentips as you get when you use the "normal' functions within a worksheet?

[adamjb]

Hi Daniel. Great lesson. Would there need to be changes with these VBA commands if you're working in option explicit?

[JanBolhuis]

Hi Daniel, Thanks for coming back.

After creating a function in VBA (used your vlookup example #12) and testing, I don't see the screentip after the (.

My function is called "Artikel". So in Excel I start with =Artikel( and after that, nothing appears in the screen. It does when I use the build-in function.

Maybe a setting? BTW: I'm working with the Dutch version.

[David-muhn]

Great video! Thanks Daniel. I got your udemy course ;)

[adamgoodrich]

Great video.
What's best way to activate workbook called "sales" if it is already open, or if it's not open then open it?

[ExcelVbaIsFun]

Hmmm, not sure what you mean. Normal function screentips? Please explain. Thanks Jan!!

[donobanmendoza]

How do I activate a book in MHTML format?

[poeticversion]

how to work enabled this with screeenupdating

[ExcelVbaIsFun]

Hmmm. Usually when I do a worksheet function within VBA or worksheet it shows these. When does it not display the screentips for you, Jan?

[JanBolhuis]

Another issue for example is that when I create a temporary macro in a new workbook, a new workbook really opens and i have to switch back to the original workbook I'm working in.

In my daily job I'm working with the Englisch version and don't have that problem :)

So it's probably a setting or a bug in the dutch version.

Or could it be the difference between V2010 (work) or V2013 (home)?

I will figure it out the next couple of weeks.