Create a New Sheet and Rename Worksheet using VBA in Excel 2010

This video shows how to programmatically Create a New Sheet and Rename Worksheet using VBA in Excel 2010

---

Closed Caption:

in this video I'll be showing how we can
use vba programming in Microsoft Excel
2010 to create a brand new worksheet
inside your work your workbook and also
renamed that worksheet to whatever name
of of of your preference so let's go
ahead and get started one of the first
things I'm going to do is go to my my
drawing tools going to be under insert
shape and rectangle that's going to add
that that's going to be my command
button right click it detects going to
put in create new sheet and this is what
i was referring to the drawing tools up
here school when you have the shape
selected gonna go to my old options that
way we have the appearance of a button
and I'm gonna go to my Developer tab
record macro to create new sheet now i'm
going to have the macro recorder help me
out a little bit i'm going to select the
new sheet button to right-click it
remain and let's call this test sheet
and i'm going to stop recording go to
macros and then create a new sheet i'm
going to click Edit and we could see
some codes already been placed in here
for us
so what we're going to keep we do we
want to keep sheets . add then after
it's going to be sheets that she's that
count
and here is where we're going to modify
the data we're not going to we're going
to change we're going to take this off
sheet she for that slight we don't
really care about selecting it because
the moment that you added that sheet is
already going to be selected sheets and
then sheet for this is what we're going
to change instead of doing sheet for
because that's not always going to be
the case when adding a new sheet it
won't always say sheep for we're going
to do active sheet that name active
sheet that name is going to return the
name of the sheet that was created and
then we're going to we're going to
rename it that name equals test sheet
and let me show you an issue that we
might run into will when we run this
macro because test shoot already exist
if we were to run the macro macro we're
gonna get an error cannot rename the
sheet so we're going to go on hit end if
we were to delete the test sheet and
then we run the macro will see that it
that it works just fine now to get
ourselves out of this problem go to your
macros and hit edit what we're going to
want to do is search to see if the name
of that sheet exist so we're going to
create a statement to go through all the
worksheets and it's going to be the work
she can't minus 1 because the index
starts with 0 if i recall correctly i'm
just going to I'm gonna double-check
this code right now I want to do message
box and i'm going to do sheets and then
rep which is the index number as this is
a for statement in case you're not
familiar with a with a for statement and
all its doing the same is going to go
from zero to whatever number this is
right here each time that the rep goes
through the rep count has changed so the
first time around be 0 then I'll be won
than to all the way until it reaches the
worksheet the count of the worksheets
minus 1 i'm going to exit sub just so i
can test this code out and go to macro
and i'm going to run it and we had an
error so this should actually be a one
and I'm going to stop this and I'm going
to play the code again
Bellamy object doesn't support this
property or method so let me see them
doing something wrong
I should have put that name so it
actually might be zero i'm going to
start over
play this code again i was telling us
that there it's out of range we change
the rep to a1 and run it we have sheet1
sheet2 xi3 sheet five and we didn't get
test sheet so let's go 2-1 I was under
the impression that the index will start
with a0 but it starts with the one so
it's going to be from 1 to the amount of
worksheets that are inside the workbook
so if we play that code again running
sheet1 sheet2 and sheet3 sheet five and
the test sheet so what we want to do is
is look to see if the name of the sheet
that we're going to create already
exists so we're going to do sheet name
to create equals and then test sheet and
then we're going to replace the test
sheet to the variable name sheet name to
create and this is what we want to do if
sheets wrapped that name equals she
needed to create then exit sub just
pretty much don't do anything because we
don't want to make a sensitive i'm going
to put L case
sheets wrapped that name compared to L
case she seemed to create so this is
pretty pretty much putting both values
into lower case if we reason they're
identical
we're just going to exit sub we won't do
anything we can even notify the user
message box this sheet already exists
and then exit sub and if now let's try
this code out right now because the
sheet name does exist it should tell us
I'm gonna run it as this she's already
exists and it's doing nothing just to
test out again this will delete that and
we're going to run the macro and here we
have a test sheet we run it again it
says that she'd already exists so let's
take it one step further go back to
sheet1 where we at the button i'm going
to use f3 as a place where the user can
enter a sheet name so i'm going to type
in home here I'm going to right-click on
the on the picture and what I'm going to
do need to click out of again and
right-click it we're going to assign
macro and I'm going to assign to create
new sheet macro and hit ok now i will
have to modify the macro someone to
click the macro and hit edit she named
to create this is going to be changed to
sheet1 . range and then we're going to
do f3 f3 . value
so let's just want to try this code out
see if it works and you work was added
and the Home tab was at it if we're to
go back and play the code again we get
an error that the sheet already exists
right guys that concludes this video on
creating a new worksheet and I guess
added that extra step in checking to see
that she name already exists and if it
does we'll give the user and error so
hope you all enjoyed the video thank you
for watching