VBA - Create New Sheets Based on Index Sheet Cell Values with Link to Index Sheet

Greetings -

I'm trying to come up with a one click VBA solution that does the following:

Takes each cell name from every non-blank cell in column "A" listed on an "index" sheet and creates a new worksheet with the same name as the sheet name. In cell "A1" on each new worksheet there should be a hyperlink labeled "home" to the original "index" sheet. There may be multiple existing sheets in the workbook, but this index sheet is always the first sheet and any existing worksheets should be ignored. Also, the "index" worksheet name, workbook name, and cell values are variable and may be different. Finally, and blank cells in column A1 should be ignored when running VBA.


Example:

An existing workbook titled "variablewb" contains 3 worksheets titled ws1, ws2 and ws3. "ws1" is the first sheet in the workbook and is our "index". In worksheet "ws1" the cells a2, a5, a6, and a10 contain the values "bob", "steve", "frank", and "andy", respectively. All other cells are blank in this example, but this can differ along with the cell values and sheet/workbook name.

When the VBA function is run:

* 4 new sheets titled "bob", "steve", "frank", and "andy" (respectively) should be created and placed in the workbook after the existing 3 sheets.
* Each of the new 4 sheets should have a hyperlink in cell "a1" that will take user back to the index worksheet.
* After the new sheets are created and the hyperlink is inserted, the user should be return to the index worksheet without the need to click on the hyperlink we created.


Hope this is enough information, and I appreciate the assistance.

Test post, was able to open both supplied files

No, 1 of our members was having problems opening this thread, we were just testing to see if there is a problem - it seems not

.
.
Paste this code into a regular module:

Please Login or Register  to view this content.

Not all of the code you were looking for. I'm looking at the hyperlink portion.

Give this a try. hyperlink will take you to the index sheet and the cell with the sheet name

Please Login or Register  to view this content.

Will take a look later this evening.