in this video I'm going to show you how
to add sheets from selected cells and
then how to hyperlink those cells to the
newly created sheets so i'll show you
how it works first of all so i have a
list of students here and then this will
be like my list of students here and
then i also have a list of their mark
breakdown and this is what their mark
breakdown would look like now I'd like
to create a sheet for every student with
this with this mark breakdown copied
onto the new sheet and I'd like the
students the names to be on the sheet so
I know where they are finally I would
like to be able to hyperlink the names
to these sheets let's go so let's add a
student's name to the list so let's type
in Joe and we'll call it black ok now I
select that cell I click the Add button
to access my macro and there I have joe
black and it's hyperlink to the sheet so
i click on Joe Black and i instantly go
to the joe black sheet so now i'll show
you how I did it so here we have my code
now so my subs name is at a new sheet
then I have defined sheet name to create
as a string SH as a workbook and SH as a
new workbook and range or n range as a
range Conte as a worksheet and o range
as a range which stands for original
range so let's see what else we've done
here now so I have there I have set the
name to create as the active cell so
it's the cell that you're clicking on is
what's the new sheets going to be called
and so then I'm setting the original
range to the active cell so that we need
to know what the original cell was and
SH equals sheets equals one is my first
sheet as sheet 1 now what I'm doing is I
am going to test
to see whether that worksheet exists and
so from worksheet one two worksheets
count so it counts how many worksheets
we have and I'm going in lower case just
make sure I didn't do a typing error and
so that it's lowercase and uppercase so
sometimes i do all lowercase or the
other so I'm checking each of these
sheets to make sure that they're not the
sheet name to create and then if it is
then I'm going to have a message box
come up and say this sheet already
exists and then I'm going to if thats
that's true that I can exit sub and now
i'm going to keep going in so this is a
loop it's a for loop and so i have next
so i'm going to keep going through my
mind sheets to make sure they're not
they're not there yet and so my sheets
that have my marks on it is called
sheets mark breakdown and I'm going I'm
caught getting it to be visible and then
I'm going to take this sheet with mark
down mark breakdown and copy it and then
I'm putting it in the last sheet so it's
counting how many sheets I have in my in
my workbook and it's putting it after
the last sheet active window active
sheet name so now i need to change the
name to active sheet name which is the
cell that we had originally selected
sheet name to create and so now i'm
making sheet markdown break break down
invisible or not visible anymore so I'm
visible equals false and now i'm
activating sheet 1 and then i'm going to
do add a hyperlink so sheet1 hyperlink
add now o' a range was my original
range which was the name that I want to
do and then I'm going to do and then i'm
going to say and then I'm adding sheet
name to create and I'm going to a one
and go to and then sheet name to create
and sheet name to create now I'm setting
oh the original range
to nothing and I'm ending sub so let's
go back and let's put in on sheet 1
let's put in oh I don't know Brenda
brown so I have tied my macro to the Add
button here so you right click on this
and you get assigned macro and then I
have assigned it to one man macro name
so now when I click on the Brenda brown
and I click on add we now have Brenda
brown and it's linked to the name and
the sheet or linked together that's how
you link and add sheets please subscribe