Adding Sheets

Excel VBA for Beginners 5- Dealing with Worksheets
11 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€92.78
List Price:  €129.90
You save:  €37.11
£79.40
List Price:  £111.16
You save:  £31.76
CA$136.11
List Price:  CA$190.56
You save:  CA$54.44
A$154.13
List Price:  A$215.78
You save:  A$61.65
S$135.08
List Price:  S$189.12
You save:  S$54.03
HK$782.28
List Price:  HK$1,095.23
You save:  HK$312.94
CHF 90.61
List Price:  CHF 126.85
You save:  CHF 36.24
NOK kr1,085.23
List Price:  NOK kr1,519.37
You save:  NOK kr434.13
DKK kr692.01
List Price:  DKK kr968.84
You save:  DKK kr276.83
NZ$167.80
List Price:  NZ$234.94
You save:  NZ$67.13
د.إ367.19
List Price:  د.إ514.08
You save:  د.إ146.89
৳10,976.08
List Price:  ৳15,366.96
You save:  ৳4,390.87
₹8,339.52
List Price:  ₹11,675.66
You save:  ₹3,336.14
RM473.25
List Price:  RM662.57
You save:  RM189.32
₦141,842.81
List Price:  ₦198,585.61
You save:  ₦56,742.80
₨27,810.04
List Price:  ₨38,935.18
You save:  ₨11,125.13
฿3,647.70
List Price:  ฿5,106.92
You save:  ฿1,459.22
₺3,232.12
List Price:  ₺4,525.11
You save:  ₺1,292.98
B$499.21
List Price:  B$698.91
You save:  B$199.70
R1,908.54
List Price:  R2,672.04
You save:  R763.49
Лв180.65
List Price:  Лв252.92
You save:  Лв72.26
₩135,197.71
List Price:  ₩189,282.20
You save:  ₩54,084.49
₪368.63
List Price:  ₪516.10
You save:  ₪147.47
₱5,633.91
List Price:  ₱7,887.71
You save:  ₱2,253.79
¥15,144.47
List Price:  ¥21,202.86
You save:  ¥6,058.39
MX$1,659.40
List Price:  MX$2,323.22
You save:  MX$663.82
QR364.31
List Price:  QR510.04
You save:  QR145.73
P1,370.91
List Price:  P1,919.33
You save:  P548.42
KSh13,148.68
List Price:  KSh18,408.68
You save:  KSh5,260
E£4,729.52
List Price:  E£6,621.52
You save:  E£1,892
ብር5,680.63
List Price:  ብር7,953.11
You save:  ብር2,272.48
Kz83,612.74
List Price:  Kz117,061.18
You save:  Kz33,448.44
CLP$97,978.20
List Price:  CLP$137,173.40
You save:  CLP$39,195.20
CN¥722.95
List Price:  CN¥1,012.16
You save:  CN¥289.21
RD$5,921.50
List Price:  RD$8,290.34
You save:  RD$2,368.83
DA13,490.83
List Price:  DA18,887.70
You save:  DA5,396.87
FJ$226.12
List Price:  FJ$316.58
You save:  FJ$90.46
Q779.86
List Price:  Q1,091.83
You save:  Q311.97
GY$20,923.51
List Price:  GY$29,293.76
You save:  GY$8,370.24
ISK kr13,946.60
List Price:  ISK kr19,525.80
You save:  ISK kr5,579.20
DH1,013.19
List Price:  DH1,418.51
You save:  DH405.32
L1,763.34
List Price:  L2,468.75
You save:  L705.40
ден5,702.11
List Price:  ден7,983.18
You save:  ден2,281.07
MOP$805.89
List Price:  MOP$1,128.28
You save:  MOP$322.39
N$1,893.44
List Price:  N$2,650.90
You save:  N$757.45
C$3,681.15
List Price:  C$5,153.75
You save:  C$1,472.60
रु13,335.63
List Price:  रु18,670.42
You save:  रु5,334.78
S/370.84
List Price:  S/519.19
You save:  S/148.35
K382.72
List Price:  K535.82
You save:  K153.10
SAR375
List Price:  SAR525.01
You save:  SAR150.01
ZK2,522.76
List Price:  ZK3,531.96
You save:  ZK1,009.20
L461.43
List Price:  L646.02
You save:  L184.59
Kč2,350.75
List Price:  Kč3,291.15
You save:  Kč940.39
Ft36,729.02
List Price:  Ft51,422.10
You save:  Ft14,693.08
SEK kr1,071.30
List Price:  SEK kr1,499.86
You save:  SEK kr428.56
ARS$85,766.82
List Price:  ARS$120,076.98
You save:  ARS$34,310.16
Bs691.04
List Price:  Bs967.48
You save:  Bs276.44
COP$387,583.68
List Price:  COP$542,632.66
You save:  COP$155,048.97
₡50,832.34
List Price:  ₡71,167.31
You save:  ₡20,334.97
L2,468.78
List Price:  L3,456.40
You save:  L987.61
₲737,805.73
List Price:  ₲1,032,957.54
You save:  ₲295,151.80
$U3,781.90
List Price:  $U5,294.82
You save:  $U1,512.91
zł400.73
List Price:  zł561.05
You save:  zł160.31
Already have an account? Log In

Transcript

Hey guys, welcome back. So in this video we're going to speak about how we can add sheets to our workbook. So basically to add sheets to your workbook, you can type sheets dot add. And you can also alternatively type worksheets dot add as well. So worksheets dot add will add sheets and sheets dot add will also add sheets and we will actually explain the difference between the worksheets collection and the sheets collection on the next video, okay, so don't worry about that at the moment. Now, if you write sheets dot add and then press space on your keyboard.

A bunch of parameters are going to appear here on the tooltip. You could also instead of pressing space, you could open a bracket and these parameters are going to appear as well. And then you can write your parameters and close brackets and you're going to be able to add sheets. Okay, so let's say you we typed sheets dot add and then we press space, you will have some parameters here, that will appear for you. So we've got the first parameter here the before parameter. The before parameter allows you to add a sheet before a certain worksheet, okay or before a certain sheet.

So you will actually give that parameter the reference of the worksheet that you need to add the new sheet before it. So let's say we want to add the new worksheet before sheet two. So we'd work worksheets, and then sheet two in the value for the before parameter. And then if we press comma, if you type a comma here, on your keyboard, you will go to the after parameter, which works exactly as the before parameter except that you will actually give the reference of the sheet that you need to insert the new sheet after it okay but you can I actually use the before and after parameters at the same time. So it's either the before or the after, but you can't use both at the same time you will get an error. So, if you put another comma, you will go to the count parameter and the colon parameter takes a number.

And this number indicates how many sheets you want to insert. So if you put, you know one, you will have one sheet inserted and one is the default value. If you put two you will have two sheets inserted and so on and so forth. So let's say we're gonna put a two here, so that we can insert two sheets at the same time. And then if you put another comma, you will go to the type parameter which we will explain in a bit. Okay, so don't worry about it at the moment.

I'll just remove the comma for it here. And you can also see that all the parameters here are optional, because they all have square bits. brackets around them. And that means that you do not have to provide these parameters for the sheets dot add or the worksheets dot add method to work. Okay, so we've written it this way, and this sheets dot add line of code will actually add two sheets before she two. So if you run that you will have two sheets added before she two.

So on the left hand side of sheet to actually sheet four and sheet five have been added as you can see here. However, if you type your code this way, it will not be easily understandable for other people who read your code, because people do not normally memorize the order of the different parameters. So you're here you haven't specified your parameters explicitly. Okay? So it's a better coding practice. It's a better practice to actually explicitly specify your parameters.

So to write for example, before colon equals worksheet sheet two, and then you would write a comma, for example, and then count colon equals two. So this way, whoever reads your code will understand that you're trying to add some new sheets. Okay? one sheet or more, you're trying to add them before sheet two, and you're trying to add two sheets. Okay, so anyone who reads this code is going to be able to understand what you're trying to do. So it's a better coding practice, in general, to explicitly specify your parameters this way with the colon equals, and remember guys parameters are like adverbs.

They explain how the verb is done. Okay? So the verb here is the ADD. The Add method is considered our verb and our adverbs are before and the count. They describe How the method or the verb is going to be done. Also, one of the benefits of explicitly specifying your parameters is that you do not have to type them in the same order as the order specified on the sheets dot add method.

So for example here, we could write the count parameter before the before parameter. So it right count equals two comma before equals worksheets, sheet two. So this way, the macro will work as well. So if you run that, we will have two sheets instead of before sheet two here on the left hand side of the sheet two and actually alter sheet four on the right hand side of sheet four. So if you run that, you'll see here that sheet six and sheet seven have been inserted before shih tzu. Okay, so that's another benefit of explicitly specifying your parameters that you do not have to write them in the same order that is specified by the method.

Okay, so let's move on to the title. parameters so we're gonna write type colon equals. And then actually to have a list of the sheet types, you're gonna write excel sheet type, and then a dot and then you will have the options for the different sheet types that you can insert. Okay, there is actually something called a chart sheet. Okay, and if you run that will actually have two chart sheets inserted before she too so they are going to be inserted on the left hand side of sheet two and on the right hand side of sheet six. So as you can see here, two charts sheets have been inserted.

And this is actually reflected. When you right click on any sheet and click on Insert. You will have the different sheet types that you can insert here there's a chart sheet there is a macro sheet so there is actually an option to insert a macro sheet as well. So excel sheet type, there is a macro sheet which you probably have never used We don't use these regularly, your chart sheet as well is rarely used. But I just thought of letting you know that there is an option to do that in VBA. Okay, so now you know how you can select a different sheet type, the default value is actually Excel worksheets.

So that means that if you don't specify the type, and as we've seen before, the default sheet type is the normal worksheet that we are all familiar with. Okay, guys, so let's see how we can do something else. I'll just comment this line of code so that he would not be executed. And something else that we can do is that we can actually insert a new sheet before the very first sheet in the workbook. And the very first sheet in the workbook is actually the sheet on the very left hand side. So remember, guys, when we were speaking about one way of selecting an object from a collection, which is using its index number, and we said that this is not a very very favorable way of doing it.

When we were speaking about the example of kicking the first bowl, regardless of what the bowl is, and if the bowling bowl is the first bowl on the very left hand side, then this is actually going to hurt, right or something is going to go wrong. So here we're actually going to do that we're actually going to select a sheet based on its position, okay, not based on its name. So we're actually going to add a new sheet before the very first sheet on the workbook, which is the sheet on the very left hand side, we're going to write sheets dot add, and then we're going to put a space and then before colon equals sheets, and then open brackets and then one and then close brackets. Okay, so this means that actually we're going to add a new sheet before the very first worksheet in the workbook and the very first worksheet is the sheet on the very left hand side.

So if you run that, you will see here that sheet eight has been inserted before sheet three, which was the very first sheet on the workbook or the sheet on the very left hand side. And of course, only one sheet has been inserted, although we haven't specified the count because the default value for the count is one, okay? Okay, something else that you can do. Another trick that you can learn is actually to add a new worksheet after the last worksheet in your workbook. Okay, so the last worksheet on your workbook is the sheet on the very right hand side here of your workbook. So to add a new worksheet after we're going to use the after parameter, and we're actually here to specify the number we're going to use the sheets dot count property, right because the sheets dot count property will yield the number of sheets that are in our workbook Okay, and this will work regardless of the number of sheets.

So this is something that will make it dynamic when you use the sheets count because you will always guarantee that a new sheet will be added after the last worksheet on your workbook which is the sheet on the very right hand side of your workbook. And you can make it before as well. So this will make a new sheet be added in the place just before the last one. Okay all the time. So if you run that, you will see here that a new sheet will be added after the last worksheet in your workbook and if you run it again, sheet nine has been added. So if you run it again, you will see sheet 10 added after it, and so on and so forth.

Okay, so this will add a new worksheet to your workbook in the very last position, which is in the position on the very right hand side regardless of the number of sheets on your workbook. Okay, so that's it guys on how to add worksheets. Thank you very much for watching this video and I'll see you on the next one.

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.