VBA to incorporate a hyperlink to another tab

Hello!

I'm new to VBA and need some help. I have a spreadsheet with many tabs (one for each deal I'm working on) all based off of a Template. I click a button and this macro (below) creates a copy of the template and prompts me to rename it to my new deal name. I'm making a 'home page' to make it easier to navigate and all my deal names will be in column E. What I want is to be able to run this macro, click the deal name I've just typed in column E, create the new template tab for this deal, AND hyperlink to the tab it just made. What I'm missing here is the hyperlink part. Any help would be much appreciated!

Please Login or Register  to view this content.

This macro will create the hyperlink in your Home Page tab after the last hyperlink in column E.

Please Login or Register  to view this content.

Do you mean that in your home page, a specific cell in column E would already be selected before you start this macro?

This modification will make it works well with spces in tab names.
Also, it will create the link to the active cell in column E of the Home Page tab.

Please Login or Register  to view this content.

OK, I think I understand.
You actually do not want the inputbox asking the sheet name as in your code.
The cell selected in column E will already have the new tab name. Right?
What is the problem of selecting this cell first before running the macro?

Now I think I got what you are looking for.

Please Login or Register  to view this content.

It should be it.
Hope this helps.

PERFECT! As to why I didn't want to be required to select the cell first - its because my boss is an older guy who is not technologically savvy. Trying to keep it simple. This is just what I was looking for. Your code above had a typo in it where it was still referencing sName in one spot. I changed it to Rng1 and its good to go. Thanks for your help on this!

Final vba if anyone else wants it:

Please Login or Register  to view this content.