Excel VBA Basics #7 - Using specific sheets, Hiding, Unhiding and Selecting with VBA

In this episode, we learn that macros can affect ANY worksheet, regardless of which one you're currently in. We learn how to control and direct the code toward a specific sheet so that our data isn't overwritten on the ACTIVESHEET. By default, excel vba will throw your Range() or Cells() object on the current sheet (Activesheet), but harnessing the power of Thisworkbook.sheets() will prove very effective.

We'll even show you how to read and write on a HIDDEN worksheet. We also show how to programmatically hide or unhide a worksheet using VBA and selecting whatever sheet to be viewed.

**Limited Offer** Learn to Make Excel Do Your Work For You with The Ultimate Excel Programmer Course – Get the Full 9+ Hour Premium Course for 75% off using coupon code: https://www.udemy.com/ultimate-excel-... Click Now to Order and get Lifetime Access to Course, Workbooks, Updates and Support!
------------------------
Create Your Own Barcode Lookup System Using Excel VBA. Learn to Make Your Own Barcode Labels the Easy way and have fun with Barcode Scanners to Automate your Workflow! In this project-driven Course, you’ll learn to Build your own Custom Inventory System with Step-By-Step video instructions. This goes in depth into some advanced Userform strategies that you can use for your other projects to come. A great weekend study! YouTube Subscibers only pay one forth! Get your 75% off Coupon here: https://www.udemy.com/barcodes-excelv...
------------------------
Too busy to Learn Piano? Use my Secrets and Play Your Favorite Songs Today! Did you know; You can learn 4 basic chords in about 5 minutes and instantly be able to play millions of songs. Seriously, check this course out. 75% off for my YouTube Friends! Click here: https://www.udemy.com/play-piano-by-e...

For Articles, Updates and to Contact me for Consulting or just to drop me a line, please visit http://www.ExcelVbaIsFun.com

Here is my link to download my FREE workbooks, past present and future: https://www.dropbox.com/sh/nwc5trm2xw...

------------------------
Playlists:
Excel Vba Basics http://www.youtube.com/watch?v=AIhKNN...
Tips N Tricks https://www.youtube.com/playlist?list...
Userforms https://www.youtube.com/playlist?list...
Sample Excel Programs https://www.youtube.com/playlist?list...
Events Series https://www.youtube.com/playlist?list...
Activex Controls https://www.youtube.com/playlist?list...
------------------------

Grab an Excel Nerd T-Shirt or Hoodie:
http://ExcelVbaIsFun.spreadshirt.com/

------------------------
Interact and Follow me on:
LinkedIn: http://www.linkedin.com/in/danielcstrong
Facebook: https://www.facebook.com/Excelvbaisfun/
Twitter: https://twitter.com/ExcelVbaIsFun
Google Plus+: http://www.google.com/+ExcelVbaIsFun


'Royalty Free Music by http://audiomicro.com/royalty-free-mu...

---

Closed Caption:

welcome back youtubers this is daniel
strong with excel vba is fun and this
lesson we're going to go back to our
code that we were doing earlier
however today we are going to talk a
little bit about that this workbook that
sheets property
one thing i want to show you is if if we
run this macro so i'll hit alt f eight
and let's run
test one that's the one that we did to
autofill this stuff with them a few
details there
so if we run that we see that it works
just fine however what if we all wanted
to confine that to this worksheet and if
i click on sheet2 and hit all f8 and i
run it right now the way we set it up
it's going to work it's going to
automatically work no matter what sheet
i have on
so what we want to do is we want it to
only happen on sheet1 no matter where
I'm click that and over what cell and
clicked on or what worksheet is open and
this'll blow you right not even at won't
even matter if the sheet is hidden or
not so let's experiment with that a
little bit
right right now will give sheet1 name
will call it my report
okay that's our sheet one's name is my
report
we're going to go back to visual basic
alt f 11 ok so test one is our macro
will stick with that name for now and
we're going to change anything other
than
and we are going to and we're going to
hard code the name of the sheet in it
so to do that I'm going to show you you
can use the sheets property by typing
sheets and open parenthesis and we're
going to also give some space here for
now to do a quote you type in the name
of the sheet that you gave it
we gave it my report in quote in
parentheses want to say sheets my report
. cells x 1 block
so that's that's a preliminary step that
will only get so far if you have several
workbooks open you accidentally click on
the other one and you run the macro it
might write over what you have on their
second workbook with that macro
so i take an additional precaution you
probably don't have to do this
I i say this workbook . sheets my report
sales blah blah blah and I do that a few
more times i'm probably end up copying
and pasting this work like that sheets
my report and later we'll show you how
you can actually save yourself a lot of
time by giving that declaring are
setting a variable name to the sheet
name and then you can just are you a
shortened version of this to represent
this work with that sheets my report
so anyway I will go ahead and copy that
for the time being
before the word cells copy with the .
and this work for that sheets blah blah
so this time each code that runs is
going to run on the sheet my report no
matter where I'm selected at so let's
try that
I'm gonna go to sheet2 and i'm going to
hit alt f 8 i'm going to go ahead and
run my code and sure enough it looks
like it wrote it on the correct sheet
let's check it out and it did
okay just forget measure will go to
sheet3 all that fate
double click here and it ran on my
report
so that's pretty cool what else have we
got here is let's even let's even go
ahead and hide it i will right click
here and click on hide and so now
they're the sheet called my report is
hid all the fate
we're going to run it anyways
double-click let's unhide right click on
one of these unhide it son had my report
and sure enough it ran
regardless of whether it was visible you
can't do that in regular ol excel click
around
so that's why vb is so important it will
run in the background it will run it
visibly just for fun let's go ahead and
do something here
let's take this workbook that sheets my
report . visible equals false
that's just going to hide this sheet is
not going to make it super hidden soup
really a very hidden is a state where
the worksheet will not will not be
visible must use visual basic to show it
again
we're just going to make it hidden but
where you can still right click and
unhide it
so we've modified the code to hide the
sheet every time this code is run
let's try that will be fun all f eight
and we'll run test 1 and if it works at
the end of the code it will hide the
sheet for us
let's run it BAM it's hidden so it's
unhide my report
ok so that was fun let's make another
macro to to make it visible again will
come down here and we'll call this one
oops we'll call this one unhide sheet so
sub unhide will call on hide my report
ok so i have to do is we'll put a code
that says this work that sheets . or
whatever my report not visible equals
true
a lot of times you'll see this somebody
is going to be true or false
it's called boolean
so a lot of these properties the visible
property of the sheet called my report
is now going to be true and we run this
one
previously we turned it to be visible
property to be false and that's what
makes it go invisible so pretty cool
let's try it let's click
let's first of all were from any sheet
here will run the code all the fate will
go to test one that was our first code
sure enough it hit it
now we'll hit alt f eight that's unhide
my report
I'm a double click here and sure enough
it done hit it
it did not select it because we were on
sheet2 at the time when we ran it if we
wanted to unhide it and then immediately
selected we would do this with our code
let's go to this code here will say this
workbook that sheets my report . select
and that's the code - oops that's the
coaches have excel select that sheet
so rather than unhiding at the jumping
fact sheet to it will actually select it
let me just hide it for good measure all
tough 81 hide my report and selected
incidentally let's go and run that
Shazam it ended and selected that sheet
so that those some nifty codes you use
okay that's about it for this video on
the next segment we're going to talk
about declaring variables which is
important especially whenever you're
setting objects like the worksheet we
want to
we'll talk about shortening that save
your fingers a lot of keystrokes
we'll also talk about
well we'll get to that when we get to it
after i was going to say
i will talk about this properties menu
down here we're going to talk about
renaming some of these things and then
eventually we'll get to some work sheet
of inch this workbook events and we'll
get to user forms at some point in time
thank you so much for viewing
god bless