How to automatically add a new sheet and Hyper link it to an Index sheet?

Those who know basics in excel do know how to hyper link different sheets within a workbook. But I am still going to brush up! 

What is a hyper link?

A hyper link is a reference to a document that the reader can directly follow.

Hyper links are generally useful when there are many sheets in a single workbook. This allows a 

degree of flexibility to navigate through the sheets from a single point.

How to create a manual hyperlink?

I have a worksheet with three sheets named after the first three months of the year. Now I want to hyperlink from another sheet named Index.

Right click on any of the cells and select Hyperlink.

Select 'Place in this document' option and click on the respective sheet on the dialog box. The hyperlink is created.

Imagine creating manual hyperlinks to many such sheets. Its going to consume some time and patience of yours! So to make it a bit more exciting Visual Basic comes into picture.

To use this option I primarily need some setup!

1. A source for the name of the sheet

2. A VB code (Macro)

3. A button to activate the Macro.

In the snapshot below I have used cell B1 of the Index sheet as a place where I will enter the name of the to be generated sheet. I have used the shapes option to create a button. I will later assign a macro to this button.

Press Alt+F11. You will enter the Visual Basic Editor.

Go to Insert and select module.

Paste the following code under it.

For those of you who are new to VB check out the procedure at http://excellence2007.blogspot.com/2010/11/how-to-convert-numerical-value-to-words.html

-----------------------------------------------------------------------------------------------------------------------------------

Sub AddHyperlinkedSheet()

Sheetname1 = Worksheets("INDEX").Cells(1, "B").Value

'THE SHEET NAME TO BE GENERATED IS ENTERED IN CELL B1 OF THE INDEX SHEET'

If Worksheets("INDEX").Cells(1, "B").Value = "" Then Exit Sub

'IF THERE IS NO NAME PRESENT IN CELL B1 THEN EXIT THE CODE'

If Application.WorksheetFunction.CountIf(Worksheets("INDEX").Range("A:A"), Worksheets("INDEX").Cells(1, "B").Value) > 0 Then Exit Sub

'IF THERE IS ALREADY A SIMILAR NAME PRESENT IN COLUMN A THEN EXIT THE CODE'

Sheetname2 = Replace(Sheetname1, " ", "_")

'IF THERE ARE ANY BLANK SPACES IN THE NAME ENTERED IN CELL B1, REPLACE IT WITH UNDERSCORES'

Sheets.Add After:=Sheets("INDEX")

'IF THE NAME IS UNIQUE THEN GENERATE A SHEET AFTER THE INDEX SHEET'

ActiveSheet.Name = Sheetname2

'THE NEW SHEET WILL BE RENAMED PER THE NAME TYPED IN CELL B1'

Worksheets("INDEX").Select

Rowvalue = Worksheets("INDEX").Range("a65536").End(xlUp).Offset(1, 0).Row

'CHECK FOR THE NEXT AVAILABLE ROW IN THE INDEX SHEET AND RETURN ITS VALUE'

Worksheets("INDEX").Cells(Rowvalue, "A").Select

'SELECT THE ROW UNDER COLUMN A'

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=Sheetname2 & "!A1"

'ADD HYPERLINK'

Worksheets("INDEX").Cells(Rowvalue, "A").Value = Left(Worksheets("INDEX").Cells(Rowvalue, "A"), Len(Worksheets("INDEX").Cells(Rowvalue, "A")) - 3)

'TRIM CELL REFERENCE FROM THE NAME'

Sheets(Sheetname2).Select

End Sub

-----------------------------------------------------------------------------------------------------------------------------------

Close the VB editor and go back to the excel sheet. On the button, right click and assign macro and click OK.

Now enter the desired name of the sheet in cell B1 and click on the button. A new sheet will be generated and a hyper link will be created in the Index sheet.