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 in the project Explorer with the mouse choose insert module and I'm going to rename our module as mod moving between sheets.
We'll need a quick subroutine then so I have a sub move - named worksheet it's enter a couple of times in tab once and we're ready to start writing code so for our example let's imagine that we wanted to move to the sheet 2 worksheet in our workbook in order to do that I first of all have to refer to the sheet 2 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 worksheet I can do that fairly quickly and easily actually you're using the intellisense if 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 it in now 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 that I want to refer to so sheet 2 in this case close double quotes close around brackets and then type in a.
. and there are two methods I could use for moving to sheet 2 the one I'm going to use is called activate so there's a simple way to move to a specific named worksheet object just to show you that this one works let me just then change the height of my screen a little bit so we can see Excel 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 when I execute this line you will see that we move to sheet 2 and there it.
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 1 again in double quotes on round brackets and then a full stop and again I can use the word activate or the activate 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 chart 1 sheet tab when that line executes 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 a chart but there is a way to generically refer to any sheet tab using another collection simply called sheets so we have a new subroutine here I'm going to 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.
Sorry open brackets and double quotes and then again the name of the specific object I want to refer to sheet 3 in this case and then dots activate and I can use sheets as well to refer to a chart sheet so if I wanted to then subsequently move to chart one I can say sheets chart 1 dot activate so it's exactly the same syntax it's just a shorter word to type in and a more generic way to refer to sheet objects if I use f8 to step through this again if.
We watch at the bottom of the screen we should see that we've moved first of all to worksheet 3 and second of all to chart sheet 1 personally I always think it's worthwhile being as explicit as possible when you're writing code so I very rarely tend to use the sheets collection I more commonly use worksheets when I'm referring to a worksheet and charts when I'm referring to a chart 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 should again simply see that we move to the appropriate worksheet so there we go so select when you referring to single sheets select has absolutely no difference to activate they're completely interchangeable one thing you can do with a select method.
Posts Related:
- New Logo Mockup PSD Files Free Download For Photoshop Sheri Sk Photoshop Tutorial
- VBA to import data from SharePoint to Excel. SharePoint Automation using VBA - 4
- SQL Server Queries Part 2 - ORDER BY (Sorting in Queries)
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
To demonstrate that let us have a quick extra subroutine down here at the bottom we'll call it select multiple sheets and I'm going to start in here by selecting worksheet one so I could of course use sheets to this but I'm gonna use worksheets instead so sheet 1 dot silic once I've selected sheet 1 I then want to include sheet 2 in the selection as well so I can then say worksheets sheet.2 dot 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'd select one first and then I would simply go on inside sheet 2 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 the word.
False here tells Excel not to replace the current selection so what it will do instead is extend the selection if I step through the routine again now using the f8 key we'll see that we've gone to sheet one while execute this line now select sheet 2 without replacing the selection you might just be able to make out at the bottom of the screen that it extends and includes sheet 2 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 mo in Excel so you see the word group.
Whenever you have more than one sheet selected so yeah that's how you can 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 blast 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 execute might move to named worksheet subroutine again that will move to sheet 2 but if she didn't already exist what.
If somebody had gone into Excel and they'd renamed sheet 2 as something else I don't I didn't actually mean literally but I ended up flying using later anyway and if I go back to my VB editor again 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 standard runtime error message subscript out of range I could click debug to show me the line that caused the problem but I already knew that anyway so what I'm going to do is stop running the subroutine by clicking the reset button so if we're.
Not going to refer to worksheets 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 going to call it mod referring to sheets and a quick separating 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 Hong if I'm going to refer to a worksheet let's say worksheets sheet 1 so that's going to.
Refer to a worksheet by name another technique you 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 sheet 1 in my case because it is the first worksheet - worksheet one sheet something else is worksheets 2 and G 3 is worksheets 3 so.
If I wanted to refer to my second worksheet I could refer to it as worksheets open parenthesis and then simply type in the index number of the objects that I want to refer to if I say dot select that will then select that object so if I use f8 to step through this point we should see that when I execute this line we move to sheet 1 and there it is and when I 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.
Numbers to the first of 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 sheet 3 using an index number if I'm using the worksheets collection then I would use the number 3 if I was using the sheets collection however the sheets collection contains all of the sheet tabs chart sheets included so what I would need to do is refer to sheets for so just to donate that worksheets sheet 3 so back button worksheets 3 and then sheets four.
Would refer to the same objects so if I step through the Regina
Game well she won more such e1 worksheets three will move to sheet three and then sheets four if I can actually just quickly demonstrate this by manually selecting a different worksheet first sheets four will also select sheet 3 so who commits potentially quite confusing which make sure you're aware of which collection you're referring to if you're going to use the index numbers.To refer to in select sheets now using index numbers to refer to sheet objects can be just as I'm reliable as using the sheet names themselves what if somebody moved your worksheets around or inserted new sheets or deleted sheets the numbers you've used previously might not refer to the worksheets you're expecting the next time you're in the code so there's one final method I'd 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 chart sheets in Excel is that they actually have two different names so you'll be familiar with a tab name of a worksheet one that you've used in Excel but each worksheet in each chart sheet has a code name as well it's easiest to demonstrate if I select the one that I've already changed the name of so sheet 2 as it's.
Called here which I've labeled as something else if I select that object in the project Explorer and look at the properties window you'll find that at the top of the properties window is a property called name just like the name of a module as we've been renaming those objects but also further down is the the name that we've given it in excel itself so you can actually change this second name of property here the tab name to do anything at all and you'll see that when.
You hit enter it updates them the sheet tab name in excel the second name that we have here the name that's 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 2 that's a direct reference to that object I can type in a full stop and I'm even presented with the intellisense so I don't have to guess anymore about which methods and properties I have to use so sheet 2 not activate will simply do that if I use f8.
To step through the routine you'll find that it activates the sheet whose name whose tab name is called anything but in VBA terms the object name the code name is sheet 2 there's absolutely no reason you have to stick to the default names by the way either if sheets to is it is fairly meaningless and XI 1 and so on sheet 1 I might perhaps want to 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 VB editor select sheet 1 in the object Explorer 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 a with a two letter code or a three-letter code sometimes SH or sh t for sheet I tend to use WS for worksheets again I try to distinguish between charts and worksheets so WS movies perhaps let's call it the bluest movies and again once you've named that object in the the.
Project Explorer you can then simply refer to that object name in code as well so WS movies dots activate another that will reliably always take me to that worksheet the advantage I guess is here that it 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 what your code is actually doing so we've seen various ways to select and refer to worksheets and charge sheets what other things can we do to 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 we can do two sheets in VBA we can insert new sheets delete 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've just added we'll head back to the VB editor to do this and I think we'll have a new module as.
Well so the insert of module and I'm going to call it mod inserting sheets and click new subroutine at the top 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 dot add if I step through this using the f8 key we'll see that when I execute this line of code the new worksheet appears to the.
Left of whichever one is currently selected so I hit f8 I'll get a new sheet to the left of in this case the movie list sheet okay 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 if I type in a space after that it exposes a tooltip which shows 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 or indeed a chart 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 but before argument is a reference to worksheets XI 3 try that again G 3 and if I step through this code now you should see that the new worksheet when I execute this line appears to the left of the one that I've referred to here so there he goes I've got a new worksheet to the left of the one that I've.
Referenced in the before argument now the after argument works in a very similar way but you can only specify either the before or the after argument not both so I wanted to modify this line of code so that inserts a sheet after she 3 what I can do is change this argument into the B after argument one technique for doing that will be to simply type in commerce and hopefully you can see from the video that if I type in a comma and it skips over the before argument and goes to the after.
Argument so you can see that it the after argument is is highlighted in bold sometimes that can get a little bit confusing just using simply commas to skip over arguments sometimes it's actually better to name the argument that you're trying to specify so what I can do instead 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 f8 to step through this I want to go it again we should see that the new work she now is inserted after sheet 3 and.
There it is now what if you wanted a system that 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 what I'm going to do is I'm going to add a new line to my routine which is going to add one worksheet at the beginning of the worksheet list and one worksheet at the very end so for the first line I'm going to insert this before referencing.
The before argument here is actually a little bit unnecessary but I do this 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 index number one so if I write the line of code which says add a new worksheet 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 index number of the last sheet is and in fact if 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.
A full stop 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 whose index number is equal to the count of all of the sheets that was ensure that it always gets added at the end of the sheet list so let's use a the fa key to step through this routine 1xq the first line I should find it new sheet appears at the start of the sheet list and there.
It is and the next sheet will appear at the end of the sheet list I might just need to tweak that so you can actually see it up here my hits F 8 there it is and it doesn't matter how many times I run this this line will always make a worksheet appear at the start this line will always make a worksheet appear at the end now it's also possible to insert more than one worksheet 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 of our list and I'd like to now insert.
Three new worksheets at the end of the list of worksheets so I need to specify another argument of the add method if I type in a comma after the after argument I can see that I now got a selection of other arguments and the one that I want to use here is called the count argument so I'm going to say count : equals and the number of watches that I wanted to add is simply three if I step through this routine using the fa key and again I'll just tweak this this view so you can see the new sheets appear 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 so it's as simple as that now as well as inserting new worksheets you can also insert a new chart sheets so I'll have a quick extra sibley team called add new charts and we'll show you how you 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 similar side charts dots add I'm going.
To specify where the new chart will go again using the I'm gonna use the after argument going to say after : equals I'm going to refer to charts chart 1 so that will insert after the work that the chart sheet called char 1 now it's also possible to use the generic sheets collection to do this so let me reference the sheets collection and I want to say sheets dot add now this in effect has exactly the same event as.
Worksheets dot add if you don't specify any extra arguments when you say sheets add it inserts a new worksheet to the left of whichever sheet is currently selected but there is another extra optional argument that I haven't mentioned yet the type argument type lets you control which type of sheet you're going to insert into your collection so I'm going to reference the type argument and say : equals and if I look for excel sheet type dot.
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 insert a cheat sorry bad sheet a chart I'll use the reference to excel chart so both of these two lines of code now we'll insert a new chart sheet if 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 it's going to insert a new chart sheet and it's going to insert it to the left of whichever sheet is currently selected and in this case happens to be the chart that I've just created so I should see a new chart get inserted to the left of chart two and there we go so that's how you insert a new chart you can just use the sheets collection sheet start add in place of worksheets dot add or charts dot add and but it's always worthwhile specifying which type of object you're then going to insert okay so now that.
We've seen how to insert sheets let's have a look at how we can delete them as well let's insert a new module for this I'm going to right click somewhere in my project and choose insert module I'm going to call my new module Maude deleting sheets the subroutine that I'll add in there will be called all the first subroutine at least what we call sub delete specific sheets I'm going to start by deleting worksheets sheet 5 so.
Again to do that I need to refer to this specific object I want you to refer to so worksheets open parentheses in double quotes sheep five and then closed quotes close parentheses followed by a full stop and I simply want to apply the delete method so when I use f8 to step through this routine I'll find 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'll get a little warning that maybe data in the sheet that you are.
Deleting and you have to click the daily button for that action to take place should say that the sheet 5 has now disappeared so I only hit f8 again just when the subroutine 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 tries to lead a sheet so let's show you how you can turn off those little warning messages before I try to delete a new sheet in fact what I'm going to do is I'm gonna delete sheet 1 in this example before I try to do that however I'm going to turn.
Off the warning messages so to do that on the line above the one which deletes the worksheet I want to refer to the application object so I press ctrl + space to display the intellisense and there's a reference to application dot what I'm going to do is change the display alerts property to be equal to false so 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.
