Excel VBA Introduction Part 6 - Worksheets, Charts and Sheets

By Andrew Gould

Previous video - Selecting Cells (Range, Cells, Activecell, End, Offset)
http://www.youtube.com/watch?v=c8reU-...

http://www.wiseowl.co.uk - In order to navigate a workbook using VBA it's essential to understand how to refer to, activate and select the various types of sheet. This video explains the difference between worksheet, chart and sheet objects and also demonstrates how to select, copy, move, delete and rename them. You'll also see how to change the visibility of the sheets in a workbook, including how to make sheets not just hidden, but VERY hidden!

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Integration Services, Visual Studio, ASP.NET, VB, C# and more!

---

Closed Caption:

welcome to this was all tutorial in this
video we're going to talk about how you
can work with worksheets Charleston
sheets in excel vba will start the video
by talking about the basics of working
the sheets in vba so i'll tell you how
you can refer to move between sheets
how you can select single multiple
sheets and then the difference between a
fermenter she's based on that she named
the code name and their index number
once we could all the groundwork well
then move on to show you how you can
manipulate sheets so do some of the
things like inserting and deleting
sheets including how to add to choose
what type of sheet you insert and how
many will talk then about how you can
copy and move worksheets including
between different workbooks show you
very quickly how you can rename sheets
and then finish the video with a quick
look at hiding and unhiding sheets
including a quick description of the
very hidden worksheet type so quite a
lot to do
let's get started
if you want to be able to move around in
a workbook is essential to understand
how to refer to select or activate
different sheets now there are several
different ways for referring to the
various sheet objects in a workbook and
a couple of different methods for moving
to them
let's start with a quick look at how you
could move to a single specific named
worksheet will head into the VBA editor
you can hold down the Alt key and press
f11 on your keyboard to do that and of
course we'll need a new module in which
we're going to write our code so i can
right click on the project Explorer the
mouse choose insert module and i'm going
to rename a module as mod moving between
sheets will need a quick seperti men
have a sub move to named worksheet and
enter a couple of times and tap once and
we're ready to start writing code
so for our example let's imagine that we
wanted to move to the sheet to worksheet
in our workbook in order to do that I
first of all have to refer to the sheet
to object
there are several different techniques
for referring to a worksheet object i'm
going to show you one of the most common
ones to begin with
so this involves referring to a
collection in vba refer to as worksheets
I can do that fairly quickly and easily
actually using the intellisense i press
ctrl + space on my keyboard and then
look for the word worksheets in my list
i can select that and then use the tab
key to type of him and worksheets is a
collection which refers to all of the
worksheet objects in the current
workbook to refer to a specific one
I can open a set of round brackets and
double quotes and then type in the name
of the sheet but I want to refer to
sochi to in this case close double
quotes close around brackets and then
type enough . and there are two methods
i could use for moving to sheet 2 1 i'm
going to use is called activate so
a simple way to move to a specific named
worksheet object just to show you that
this one works let me just change the
height of my screen little bit so you
can cxl in the background and if i were
to step through this subroutine using
the f8 key
each time i press f8 it moves to the
next line of code want to execute this
line will see that we moved to sheet2
and there it is
it's the first simple way to move to a
worksheet
now worksheets on the only type of sheet
object you'll find in a workbook
you might have noticed as well that we
have a chart sheet and those sheet tab
at the bottom medical chart one so we
can do something very similar if we
wanted to move to a specific named chart
have a quick extra separate in here
move to chart and rather than use the
worksheets collection
what I can do here at refer to a
collection called charts again i use my
intellisense list will find the charts
collection and if i want to move to it
I need to first of all refer to it by
name so chart one again in double quotes
around brackets and then . and again i
can use the word activate or deactivate
method to move to that object if I use
f8 to step through
we should see that at the bottom again
we'll move to the charts one sheet tab
when that line excuse
the worksheets and the charts
collections can only hold objects of the
respective type so worksheets can only
hold a worksheet and charts can only
hold the chart there is a way to
generically refer to any sheet tab using
another collection simply called sheets
so we have a new separating here
I'm gonna call that one move to any
sheet if I wanted to move to say sheet 3
I know that it's a worksheet but rather
than referring to that the worksheets
collection i can simply refer to a
collection called sheets open quotes and
I open brackets and double quotes and
then again the name of the specific
object i want to refer to
xi3 in this case and then . activate and
I can use sheets as well to refer to a
chart sheet phones - then subsequently
moved to chart wantin i can say sheets
chart one . activate
so it's exactly the same syntax is just
a shorter word to type in and a more
generic way to refer to cheat objects if
i use fa to step through this again if
we watch the bottom of the screen we
should see that movie first of all to
work sheet 3 and second of all to charge
sheet 1
personally I always think it's worth
while being as explicit as possible when
you're writing code so very rarely tend
to use the sheets collection
I'm not commonly used worksheet when i'm
referring to a worksheet and charts when
i'm referring to a charge but that's
just a personal preference
so far we use the activate method to
move to each of our different sheets but
there is another method that you could
use instead called select so if i
replace the word activate with the words
select in one of my previous
instructions and if I step through this
code you said again simply see that we
moved to the appropriate worksheet so
there we go
so select when you were referring to
single sheets select has absolutely no
difference to activate that completely
interchangeable one thing that you can
do with this select method which you
can't do with the activate method is you
can you select to select more than one
sheet at the same time so the Democrat
let's have a quick actress ability in
down here at the bottom Youkilis select
multiple sheets
i'm going to start in here by selecting
worksheet 1 so i could of course you
sheets to this but i'm going to use
worksheets instead so sheet1 . select
once I've selected sheet1 I then want to
include sheet 2 in this election as well
so i can then say work sheets sheet2 .
select again but i have to specify an
extra optional parameter for the Select
method if I just executed these two
lines one after the other
the selection would be replaced so i
select she won first and then i would
simply go on it's actually too
so what I want to do in the second time
i select a sheet is I want to set a
parameter called replace to a value of
false so though the word false here
tells Excel not to replace the current
selection sort of will do instead is
extend this election if I step through
that the routine again now using the f8
key will see that we've gone to sheet1
when i execute this line now select
sheet to without replacing this election
you might just be able to make out at
the bottom of the screen that it extends
and includes sheet to in my selection as
well
that's probably a little bit easier to
see actually if i show you that i am now
in group mode in Excel
so you see the word group whenever you
have more than one sheet selected so
yeah that's how you use the Select
method rather than the activate method
to select more than one sheet at the
same time
so far we've referred to all of our
sheet objects by name
that's by no means the only way to refer
to a sheet object and indeed some cases
it might not be the most reliable way
either
so if I xq might move to named worksheet
subroutine again that will move to
sheet2 but if she - didn't already exist
what if somebody had going to accelerate
rename sheet to as something else
I don't I didn't actually mean literally
but i ended up stabbing isn't really any
way and if i go back to my BB editor
getting and I tried to execute the
subroutine which selects sheet 2 i'll
find that it fails because she too
doesn't exist anymore so I get the sound
of runtime error message subscription
out of range
i could click d book to show me the line
that caused the problem by already knew
that anyway so I'm going to do is stop
running the 17 by clicking the reset
button
so if we're not going to refer to work
sheets by name or that can be unreliable
what other techniques have we got
available
let's have a new module to demonstrate
the various ways to refer to worksheet
so i'm going to insert a module
i'm gonna call it mod referring to
sheets and flexibility in here
methods for referring to sheets
let's start with a quick reminder of the
basic way that we've been using so far
so home if I'm going to refer to a
worksheet say work sheets sheet1 so
that's going to refer to a worksheet by
name
another technique can use for referring
to a worksheet is to refer to them by an
index number
so most collections nearly every
collection in vba is index that is the
items in the collection are given a
number
so for worksheets the collection is
indexed from left to right so sheet1 in
my case because it is the first
worksheet
- work sheet 1 sheet something else is
worksheets to and she three is
worksheets three
so if i wanted to refer to my second
worksheet
I could refer to it as worksheets open
parentheses and then simply type in the
index number of the objects that I want
to refer to
if I say . select that will then select
that object
so if i use a fate to step through this
point we should see that when I actually
this line we move to shoot one and there
it is
I want to execute this line it moves to
the something else
sheet because it's a second one in the
collection
if you're going to use index and mr. the
first sheets
you do need to be much more careful
about which collection you're referring
to
so for instance if i wanted to refer to
xi3 using an index number if I'm using
the worksheets collection then i would
use the number three if i was using the
sheets collection however the sheets
collection contains all of the sheet
tabs chart she's included
so what I would need to do is refer to
sheets for so just to demonstrate that
worksheets
sheet3 start button worksheets three and
then sheets for would refer to the same
objects
so if I step through the routine again
well she won more starchy one worksheet
three will move to shoot three and then
she's for thinking that she just quickly
demonstrate this by manually selecting a
different works you first
sheets for will also select sheet 3
so who can be potentially quite
confusing to make sure you're aware of
which collection you're referring to
if you're going to use the index numbers
to refer to and select sheets
now using index numbers to refer to
sheet objects can be just as a reliable
as using the sheet names themselves
what if somebody moved your worksheets
around or inserting new sheets or
deleted sheets the numbers you've used
previously might not refer to the
worksheet you're expecting the next time
you're in the code so there's one final
method other like to look out for
referring to sheets in this video and
that's using code names to refer to
sheets
this is absolutely the most reliable way
to refer to any sheet object
now one thing you might not realize
about worksheets and charge sheets and
XL is that it actually have two
different names
so you'll be familiar with the tab name
of a worksheet one that you use in excel
but each worksheet in each charge sheet
has a code name as well is easiest to
demonstrate if i select the one that
I've already changed the name of sochi
to as it's called here which I've
labeled as something else if I still let
that object in the project Explorer and
look at the properties window you'll
find at the top of the properties window
is a property called name just like the
name of a modulated we've been renaming
those objects but also further down is
that the name that we've given it in
Excel itself
so you can actually change this second
name and property here that tab name
- - anything at all and you'll see that
when you hit Enter
it updates then the sheet tab name in
Excel the second name that we have here
the name is listed at the top is the
code name of the worksheet and you can
use that code name directly in your vba
code
so if I said sheet - that's a direct
reference to that object i can type in a
. and I'm even presented with the
intelli sense so i don't have to guess
anymore about which methods and
properties i have to use so she - not
activate will simply do that if i use fa
to step through the routine
you find that it activates the sheet of
names tab name is called anything but in
vba terms the object name the code name
is sheet2 and there's absolutely no
reason you have to stick to the default
names by the way either
if sheet - is it is fairly meaningless
and she won and so on sheet1 i might
perhaps once you label with a name that
describes what the data contained in it
so it's the top 13 films
so if i go back to the VBA editor select
sheet 1 in the object to explore the
project Explorer
I can change the name of the worksheet
to anything that I want in the name
property you'll often find people
precede the worksheet names with it with
a two-letter code or three letter code
sometimes sh or sht for sheet i tend to
uws for work sheets again i tried to
thing which between charts and
worksheets
so WS movies perhaps as collectible as
movies and again once you've named that
object in the ad the project Explorer
you can then simply refer to that object
name in code as well as of WS movies .
activate
I know that will reliably always take me
to that worksheet the advantage I guess
is here that that is very very unlikely
that your end users would come into the
visual basic editor and start changing
the names of your worksheet objects
whereas i might still want to change the
sheet names
that's not a problem anymore because the
sheet name is irrelevant to watch your
code is actually doing
so we've seen various ways to select and
refer to work Jesus in charge sheets
what other things community of them well
if you right click on the sheet tab in
excel
that'll give you a good idea about the
various things we can do to sheets in
BBA we can insert a new sheet still eat
old ones rename them etc etc so let's
start with a quick look at how you can
add new worksheets and then rename the
sheets that you just added will head
back to the VBA editor - yes and I think
we'll have a new module as well so that
insect module and i'm going to go in mod
inserting sheets and a quick new
separate units up sub add new sheets ok
so the simplest way to insert a new
worksheet is to start by referring to
the worksheets collection
I can do that using the intellisense
list and then simply say that you want
to apply the add method to the
worksheets collections or worksheets .
add if I step through this using the FA
ki will see that when i execute this
line of code
the new worksheet appears to the left of
whichever one is currently selected one
hit f8 I get a new sheet to the left of
in this case the movies sheet
ok so inserting a new worksheet is
fairly straightforward but what if you
wanted a bit more control over where
your worksheet appeared the key to doing
this is using some extra optional
arguments of the add method by type in
this space after that it exposes a
tooltip we showed me that there are four
optional arguments for this particular
method the ones i'm interested in here
are the before-and-after arguments
each one of these allow you to pass in a
reference to another worksheet we need
to charge sheet
so let's say that I wanted my new
worksheet to appear to the left of sheet
3 i'm going to specify that before
argument is a reference to work sheets
xi3 try again
III and if I step through this code now
you should see that the new worksheet
want to ask you this line appears to the
left of the one that I prefer to hear
so there goes i got a new worksheet to
the left of the one that I've reference
in the before argument now the after
argument works in a very similar way but
you can only specify either before or
the after argument not both
so I wanted to modify this line of code
to the insert sheet after she three
what i can do is change this argument
into the the after argument one
technique doing that will be to simply
type in commerce and hopefully you can
see from the video that if i type in a ,
and it skips over the before argument
and goes to the after argument so you
can see that it
the afternoon teas is highlighted in
bold and Sons homes that can get a
little bit confusing just using simply
commerce to skip over arguments
sometimes it's actually better to name
the argument that you're going to
specify
so what i can do is that here is
actually write out the word after
followed by a colon and an equal sign
and that's also now
specifies the after argument so if i use
fa to step through this
want to go again we should see that the
new works now is inserted after she
three
and there it is
now what if you wanted a system that's
always inserted your new worksheets
either at the start of the sheet list or
at the end of the sheet list
we can do that too and again we do that
before and after arguments to control it
so i'm going to do is I'm gonna add a
new line to my 17 which is going to add
one worksheet at the beginning of the
word list and one sheet at the very end
so the first line I'm going to serve
this before
referencing the before argument here is
actually a little bit unnecessary but
ideas for the sake of completeness
I want to insert this before the very
first sheet in my collection
now earlier on we looked at the idea of
referencing worksheets or sheet objects
by their index number and we know that
the first worksheet always has in next
number one if i write the line of code
which says Adam LaRoche eat before the
first sheet in the index that will
always appear as the new first worksheet
and we can use a similar trick to insert
a worksheet at the end of the sheet list
now
the difficulty here is you don't always
know what the in next number of the last
sheet is
and in fact I've just inserted one
worksheet on this line then the index
number will have changed by the time I
reach this line so a neat little trick
is to reference not a specific number
but a property of the sheets collection
if I reference the sheets collection
here and then type in . you'll find that
it has a property called count and count
always tells you how many objects there
are in that particular collection
so if i say insert a new worksheet after
the sheet
who's index number is equal to the count
of all of the sheets that will ensure
that it always gets added at the end of
the sheet list
so let's use a BF a key to step through
this routine or next to the first line
I should find a new sheet appears at the
start of the sheet list and there it is
and the next sheet will appear at the
end of this time I just need to tweak
that so you know you seem appear
hit f8 there it is and it's the matter
how many times i run this this line will
always make a worksheet . to start this
line will always make it work she'd
appear at the end
now it's also possible to insert more
than one working at the same time so i
can use another of the optional
arguments of the add method to specify
that let me just get rid of the line
which inserts a sheet at the beginning
about list and I'd like to know insert
three new worksheets at the end of the
list of worksheets
so I need to specify another argument of
the add method i type in a , and after
that the after argument i can see that I
now got a selection of other arguments
and one that I want to use here is
called the count arguments on this
account
: equals and the number of which is that
i wanted to add is simply three
if I step through this routine using the
fakie and again I'll just tweak this
this view so you can see the new sheets
of here
when i run this line of code you should
simply see that three new sheets get
inserted at the end of the list in one
go
such as simple as that
now as well as inserting your worksheets
you can also insert new charge sheets
sort of a quick extra security team
called add new charts and we'll show you
how to do that too
so there's a couple of different
techniques that you can use to do this
we could as we've done by referencing
the specific worksheets collection we
could reference the specific charts
collection and simply said charts . ad
and i'm going to specify where the new
chart will go again using the uneasy
after argument is a after a colon equals
I'm going to refer to charts job one
that will insert after the work that the
charge sheet called char one now it's
also possible to use the generic sheets
collection to do this let me
reference the sheets collection and i
want to say sheets . add now this in
effect has exactly the same event as
worksheet thought that if you don't
specify any equity arguments when you
say sheets . add it inserts a new
worksheet to the left of which have a
sheet is currently selected but there is
an other extra optional argument that i
haven't mentioned yet the type argument
type lets you control which and type of
sheet you're going to insert into your
collection
so I'm going to reference the type
argument as a colon equals and if i look
for ex-l sheet type got that gives me an
idea of the types of worksheets that I
can reference
so this is this is an enumeration excel
sheet type there are several specific
options i'm allowed to select for this
particular argument so I wanted to be
inside a cheat at sorry match eat a
chart i'll use the rapids 2xl chart so
both of these two lines of code now will
insert a new charge sheet i use the f8
key to step through this first line will
insert it directly after chart one and
there it is
now when i execute this line of code is
going to insert a new chart sheet and
it's going to insert it to the left of
whichever sheet is currently selected in
this case happens to be the chart to
have just created
so I should see a new charge and
inserted to the left of chart -
and there we go that's how you insert a
new charts you can just use the sheets
collection sheets . at in place of work
she's . add or charge . ad and but it's
always worth while specifying which type
of object
you're then going to insert
ok so now that we've seen how to insert
sheets let's have a look at how we can
delete them as well
lessons at any module for this i'm going
to right click somewhere in my project
and choose insert module
I'm gonna call my new module mod
deleting sheets the 17 that are not in
there will be called all the first
operating at least we called sub delete
specific sheets
I'm going to start by deleting
worksheets sheet five
so again to do that I need to refer to
the specific object I want you to revert
so worksheets open parentheses in double
quotes sheet five and then closed
prepared quotes close parenthesis
followed by . and i simply want to apply
the delete method
so when i use fa to step through this
routine
I'll find out when i execute this line
of code what will happen is just as
though you're going to delete this
yourself manually in Excel you get a
little warning that there may be data in
the sheet that you are deleting and you
have to click the delete button for that
action to take place should say that she
5 has now disappeared when he hit her
face again just to end this routine that
one has now been deleted
now if i was deleting lots of worksheets
in the same subroutine it could be quite
annoying to have to click delete every
single time I try to lead a sheet
so let's show you how you can turn off
those little warning messages before I
tried to delete a new sheet
in fact i'm going to do is delete sheet
1 in this example before I try to do
that however i'm going to turn off the
air
the warning messages so to do that and
the line above the one which deletes the
worksheet
once we refer to the application object
to press ctrl + space to display the
intellisense and there's a reference to
application . what i want to do is
change the display alerts property to be
equal to false
that means that the alert messages won't
appear when I perform any actions that
normally would make them appear
it's worthwhile resetting those at the
end as well so after I've tried to
delete a sheet
i'm going to say application . display
alerts equals true i make sure the
warning messages will be resets they
will be in fact after the subroutine is
finished
so when i hit f8 to step through this
regime
you won't see anything exciting happen
here when i execute this line and when I
instead choose to lead to work she now
you should simply see the way she
disappears with no warning messages and
then the warning messages of reset for
later on
when you've written a separate team
which deletes a specific named worksheet
you do have to be careful about how many
times you run it because of course once
i deleted their sheet one object once I
can't run it again to leave the same
object that object no longer exists
1x keep the line I get a runtime error
again the standard subscript out of
range
I try to refer to an object which no
longer exists I'm going to end that
subroutine
and what if I to do in this example is
delete whichever sheet is the last sheet
in the entire list
so it's not referring to the worksheets
collection I'm going to refer to sheets
and instead of referring to a specific
named object
I'm going to refer to the index number
of the last worksheet and i'm going to
use the same trick that I showed you
earlier on web i can count the number of
sheets in the sheets collection and pass
that value in the index number have the
sheet that i'm referring to
so I use fa to step through this one now
I should see that whichever is the last
worksheet will be deleted obviously
sheet 9 in this case and and I can run
this routine as many times as I like I
just click the play button a few times
you want to leave each time it will
delete the last worksheet in the
sequence that they replace two charge
sheets as well as i keep on going a
little bit further
sometimes it all there that the
worksheet objects up to that point the
last sheet object now is a chart if I
excuse that it will delete the charge
sheet as well
one final where I'd like to show you to
delete our worksheet or charts is by
deleting the entire collection in one go
this is this is the front when I'm gonna
write a simple dress to Brasilia and sub
delete all charts and what I'm going to
simply do is say charts dots
delete I could also say here worksheets
. delete and what that line of code does
deletes every single object in the
collection that I referred to in order
to make this work
you probably know already that each
Excel workbook must contain at least one
sheet either charge or worksheet so if
you're going to do this you must make
sure that there is at least one
worksheet
if you're deleting charts or chart if
you're deleting worksheets i hope that
makes sense and frightening me now when
i execute this subroutine Barrowman that
I hadn't turned off the warning messages
if I execute that line if i simply click
the delete button here
it deletes help we can see all of the
objects in the collection that you felt
be referred to and of course because you
will hopefully already know that you
can't undo what and macros
you can't get those objects back either
a potentially quite terrifying if you do
that by mistake but really powerful to
avoid having to delete each worksheet or
charge sheet one by one by one
ok so we've seen how to insert and
delete sheets let's have a look now at
how you can copy move them
let's create a new module for this i'm
going to insert a new module and i'm
gonna call it
mod copying and moving sheets let me
make sure I can spell that probably be
moving sheets and what I'm going to do
in there is first of all quite a
subroutine called copy sheets if you've
been following this so far and you've
been thinking that is fairly easy to
guess the add method and the delete
method
you won't be surprised to find that if
on to copy a worksheet and in this case
I'm going to make sure that copy the
worksheet called WS movies
probably not surprised to find that to
do that you simply apply the copy method
and and again a lot like the and the add
method you can choose where your books
CDs copy to the time in the space after
the copy method
I see you again I get the before and
after arguments
so what I'd like to do is I like to copy
my movies worksheet so that it sits
after the anything worksheet gonna say
after colon equals worksheets anything
and again I could have referred to this
with this work she's in a variety of
different ways but it was sort of mix
and match so you're happy with into
changing these styles of references so i
use the F a key to step through this
when i execute this line of code
somewhat unsurprisingly i get a copy of
my movies sheet after the anything sheet
notice it has the generic names with
with the number in parentheses just as
if you could copy this yourself manually
in Excel
now you might have noticed that the
arguments for the copy method are
optional and in fact if i delete be
after argument here and type in a space
after the copy method again the reason
you can tell that they're optional is
because the argument names are contained
in square brackets so before and after
about the optional
so what happens if I don't specify where
my new copy should go
this is a bit more complicated than just
what happens when you insert a new sheet
excelled in just insert the new copy to
the left of which have a sheet is
currently selected what actually happens
easy if I step through this this regime
what actually happens is excel sheet to
create a brand new blank workbook and
insert your copy of the worksheet into
that you might see here in the project
Explorer
I'm going to work book called book for
and if i switch into that workbook
there it is a brand new blank workbook
with one copy worksheet in it
that's quite powerful and I'm something
worthwhile knowing that you can simply
generate new workbook by copying any
work it into it
if you have more than one workbook open
it's also possible to send your copy to
a specific workbook as well so i can do
here is I can seem to be asked me please
don't copy and i'm going to say to place
the copy before workbooks the work but
that I want to refer to is actually the
one that's just being created it's
called book for and we're gonna have a
slightly longer video about how to work
across multiple workbooks and do things
like opening and closing and saving it
just for now know that you can refer to
a workbook by preventing the workbooks
collection and then the name of the
workbook in parentheses in double quotes
the same syntax effectively is how you
refer to a worksheet or a chargesheet
within that workbook and then need to
refer to a specific sheet
what I'm going to do is going to refer
to sheetz one
so what will happen is my movies
worksheet will be copied into the book
for workbook and we placed before the
first worksheet in that list so I excuse
go down and switch back into the book
for vaccines it down the bond between
there
you should see that I've got a new copy
before the original one
so that's how you copy files or so
worksheets from one workbook to another
now moving sheets works in almost
exactly the same way as copying them
have a quick new separating called a
moving sheets and as you'd expect if I
refer to the worksheet that I want to
move i'm going to use it to the US
movies again and i can simply say . move
just like the copy method works there
are two extra optional arguments where I
can specify before or after if i omit
both of those arguments the worksheet we
moved into a brand new blank workbook
but I'd like to move my worksheet so
that it sits at the end of my current
list of sheets in this workbook and AWS
movies don't move i'm going to say its
place it and after clinicals sheets
sheets . count and when i execute this
using a two step through
I should see that movie the sheet now
sits after all of the other sheets in
this workbook all of the other optional
arguments are available as well just
like we saw for copying so I again as I
said I can miss out all of the arguments
to move the sheet a new workbook or I
could specify to place it in an existing
open workbook
just as I have been here with the copy
method
ok so next let's have a quick look at
how you can rename a worksheet and then
have a brand new module this time like
living inside a module alkaline mod
rename sheets and expertise in here
called sub rename single sheet
it's going fairly short 17 and it sends
out what I'd like to do is assign a new
name to the movie list worksheet
when I talk about the name remember
early Romney sure that there are two
names for a worksheet object
there is a tab name so a standard name
that you see in Excel movie space list
there's also a code name in this case
it's called WS movies and you can't
write code to change the code name but
you can write code to change the name or
that of the tab name of the sheet name
so what I'm going to do is I'm going to
refer to WS movies I'm going to change
its name property and i'm going to make
it equal to something else again
literally something else
so if I ask you this guy right now
what you should see when this line is
executed the name movie list is replaced
with something else and there it is
now one thing to be careful of what you
don't
this of course is that if you had any
previous code which refer to work sheets
movie list which was its at which is old
name that code would no longer work
so again this is why it is so worth
while using the code names of objects
rather than things that users could
easily change themselves
ok so for the very final part of this
video we're going to talk quickly about
how you can hide and show worksheets
so again I'm gonna have a brand-new much
of this mod show and I sheets and then
in there we'll have a really simple
separating called sub hide movie sheet
simple property is used to change the
visibility of individual sheets if I
refer to W as movies and a lot of people
tend to guess that if you want to hide a
worksheet you apply the hide method to
it which is a reasonably good guess but
unfortunately that's not how it works in
excel vba if i want to hire worksheet
what I actually have to do is change a
property called visible now
surprisingly the visible property has
three different possible States
there's hidden invisible there's also
this intriguing sounding one called a
very hidden
now let me start by using excel sheet
hit and first of all if I execute code
so you have executed Scott now using the
f8 key step through what I should find
it that my WS movies which is now called
something else will be hidden and it's
gotten i can head back to excel and if I
now right click on any of the sheet and
choose unhide you should find that my
invisible something else sheet is in the
list
I'm not going to use the dialog box to
redisplay it
what I'm going to do is go back to my
code and simply change this property
here to to have rather an excel sheet
hidden and backspace that
if i press ctrl + space i can we just
lay my intelligence list
i'm going to use excel sheet visible and
run the code again and my sheet now
becomes visible again
it's worthwhile mentioning that I could
have done this as well using the
properties window by selecting WS movies
you'll see that it's visible properties
available here as well
I can change it to excel sheet hidden
and it's gone
I can have to i'm going to reselect it
again now and change that back to excel
sheet visible again and now it reappears
so those are the two fairly obvious ones
visible and hidden was this very hidden
thing all about
let me get rid of that and press ctrl
space to display my intellisense list
and i'm going to look for excel sheet
very hidden if I execute the code again
you'll find that there's something else
she tore WS movies and disappears from
view
but this time if I'm back in action and
i right click on a sheet tab
exhale doesn't think that there are any
hidden sheets there's nothing now to
unhide so your end users would not know
that any very hidden sheets were even in
your part of your workbook
the only way to redisplay that worksheet
is to use code switch back to the visual
basic editor again
I can either do this from the properties
window or i can do it from code as well
make the sheet excel sheet visible in
the subroutine and something else
reappears and is available to the user
again
so there we go we've had a good look at
how you refer to work sheets how you
move between them how you insert and
delete them
copy and move rename and finally show
and hide them
there are many of the things I can think
of that you would want to do the
worksheet in Excel
that certainly covers all the basics and
hopefully you found that useful
thanks for watching if you enjoyed this
training video you can find many more
online training resources at www.dhl.com
coat UK