How to Add and Hyperlink new sheets with VBA in Excel

How to Add and Hyperlink new sheets with VBA in Excel


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

Comments

  1. Post
    Author
  2. Post
    Author
  3. Post
    Author
  4. Post
    Author
    Nestor Alexander Castañeda Padrón

    You saved my life! That's exactly what I was looking for and I just found the solution here! Thank you very much! I'm so thankful!

  5. Post
    Author
    Suho Hope

    Hi Barb,

    I'm a newbie to VBA and was so happy to find your solution. Just one thing, when I ran the code, it kept saying 'Compile error: Variable not defined" and highlighted the "rep" in "For rep= 1 to (Worksheets.count). Could you please help explain why?

Leave a Reply

Your email address will not be published. Required fields are marked *