Auto Create Worksheets from Lists

[TD_struggles]

Hi,

I have a tab "Sheet1" complete with lists of all the information I require listed under headings.

I would like a Macro that creates new worksheets for Everything listed in "Sheet1"

The macro would select everything B5 to I82
Create a new worksheet for each cell
Name the worksheet the cell contents
Hyperlink everything in "Sheet1" to its new worksheet

I think i have explained it properly. I've tried a few variations with no success.

 

[My Aswer Is This]

Hi,

I have a tab "Sheet1" complete with lists of all the information I require listed under headings.

I would like a Macro that creates new worksheets for Everything listed in "Sheet1"

The macro would select everything B5 to I82
Create a new worksheet for each cell
Name the worksheet the cell contents
Hyperlink everything in "Sheet1" to its new worksheet

I think i have explained it properly. I've tried a few variations with no success.

So your asking for 624 new sheets to be made?

 

[TD_struggles]

So your asking for 624 new sheets to be made?

About that. There are some blanks. Due to the amount, the hyperlinks are imperative.

 

[TD_struggles]

So your asking for 624 new sheets to be made?

Its actually 226

 

[My Aswer Is This]

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[jolivanes]

Don't let "My Aswer Is This" pull your leg.
He/she is a well respected and knowledgeable helper.

Try this on a copy of your original.
Change references where required.

Sub Add_Sheets()
Dim i As Long, j As Long, sh1 As Worksheet
Set sh1 = Sheets("Sheet1")
Application.ScreenUpdating = False
    For i = 2 To 9
        For j = 5 To 82
            If sh1.Cells(j, i) <> "" Then
            ActiveWorkbook.Sheets.Add After:=ActiveWorkbook.Sheets(Sheets.Count)
                With Sheets(Sheets.Count)
                    .Name = sh1.Cells(j, i).Value
                End With
                sh1.Hyperlinks.Add Anchor:=sh1.Cells(j, i), Address:="", SubAddress:="'" & sh1.Cells(j, i).Value & "'!A1", TextToDisplay:=sh1.Cells(j, i).Value
            End If
        Next j
    Next i
Sheets(1).Select
Application.ScreenUpdating = True
MsgBox "You have " & ThisWorkbook.Sheets.Count & " Sheets."
End Sub

 

[My Aswer Is This]

Don't let "My Aswer Is This" pull your leg.
He/she is a well respected and knowledgeable helper.

Try this on a copy of your original.
Change references where required.

Sub Add_Sheets()
Dim i As Long, j As Long, sh1 As Worksheet
Set sh1 = Sheets("Sheet1")
Application.ScreenUpdating = False
    For i = 2 To 9
        For j = 5 To 82
            If sh1.Cells(j, i) <> "" Then
            ActiveWorkbook.Sheets.Add After:=ActiveWorkbook.Sheets(Sheets.Count)
                With Sheets(Sheets.Count)
                    .Name = sh1.Cells(j, i).Value
                End With
                sh1.Hyperlinks.Add Anchor:=sh1.Cells(j, i), Address:="", SubAddress:="'" & sh1.Cells(j, i).Value & "'!A1", TextToDisplay:=sh1.Cells(j, i).Value
            End If
        Next j
    Next i
Sheets(1).Select
Application.ScreenUpdating = True
MsgBox "You have " & ThisWorkbook.Sheets.Count & " Sheets."
End Sub

I always have a little trouble with Hyperlinks.
Like this line of code:
sh1.Hyperlinks.Add Anchor:=sh1.Cells(j, i), Address:="", SubAddress:="'" & sh1.Cells(j, i).Value & "'!A1", TextToDisplay:=sh1.Cells(j, i).Value

Maybe I do not want A1.
Maybe I want to Use G5 on one and G6 on another.
I would like instead of A1 for it to be Cells(5,7) and then Cells(6,7) on next one
How can I do that?

 

[Fluff]

@My Aswer Is This please do not "hijack" the thread. Either wait for the thread to be solved, or start a thread of your own. Thanks

 

[jolivanes]

Hi Fluff,
It looks like the OP is
1. Told by his wife to do the dishes and he is not finished yet
2. Has a medical emergency
3. Is still trying out the code and has a very slow computer so he's waiting for it to finish
4. Or is one of the many that once they have what they want won't border anymore with the thread.

Initially it took 12 hrs to be answered. We don't know the time zones involved so it can be considerable shorter then that.
It's a day and a half in the meantime so take any of the 4 possibilities mentioned above, or any other one for that manner, and I would say that the thread can be closed.
In the meantime I hope it is within the rules, because of the above mentioned possibilities, to answer "My Answer Is This" and for future people reading this.
Change the A1 to the cell or range you want, it'll select it when opening the sheet through the hyperlink on Sheet1.
I've tried Both "'!B12", and "'!C15:F25", and both worked.

If not, tell me to go and repent in the corner for 15 minutes.

Thanks.

 

[Fluff]

Answering MAIT's question is fine as the OP seems to have disappeared.