Numerically Ordering Sheets

[Jetx227]

Hey guys,
So I have report workbooks where I have a bunch of items that have estimate sheets for. Each item is identified as a number and has a worksheet dedicated to each one (named by the number of the item). The first sheet in the workbook holds the summary table (so all the item numbers are in one column). I made a macro that creates a sheet for each item (because I have like 50 to 100+ sheets I could work with at a time, so it makes it much easier) and we tend to add items as we go so the macro can read the numbers and see we already have a worksheet for that item number. The only problem is that navigation can get hectic so it's nice to have them in numerical order to make things a bit easier, but since we go back and put new items in later, plenty of the sheets get out of order and mixed up and it takes a while to move them to the correct spots. I was wondering if anyone could help come up with code that I can add on to the macro that I have already that can sort the item sheets into numerical order (they all have numbers as names) but leave the summary and template sheets alone at the front of the workbook. Any suggestions would be much appreciated. Thanks!

 

[Jetx227]

Ok, sorry I misunderstood it, yes it wouldn't be needed.

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Ok, sorry I misunderstood it, yes it wouldn't be needed.

 

[Jetx227]

If you would like a easy way to navigate your sheets try this script on a empty workbook with several Sheets.
Name your first sheet Master
And Then run this script.
The script will put a list of all your sheet names in column A of sheet named Master
You can look at the top of the script and change the sheet name if you want.

And each sheet name in column A will have a Hyperlink added. If you click on the Hyperlink you will be taken to that sheet.

In Range("A1") of each sheet there will a link which if clicked on will take you back to the Master sheet.

Give it a try if you want and see how it works.

Sub AddHyperLinks()
'Modified  7/11/2018  3:25:10 PM  EDT
Dim C As Range
Dim i As Long
Dim ans As String
ans = "Master" 'Modify this name if needed all sheet names will be put in Column "A" of this sheet
Sheets(ans).Activate
Sheets(ans).Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Clear
    For i = 2 To Sheets.Count
    Sheets(ans).Cells(i, 1).Value = Sheets(i).Name
    Sheets(i).Cells(1, 1).Value = Sheets(ans).Name
    Sheets(i).Cells(1, 1).Hyperlinks.Add Anchor:=Sheets(i).Cells(1, 1), Address:="", SubAddress:="'" & Sheets(i).Cells(1, 1).Value & "'!A1"
    Next

With Sheets(ans)
    For Each C In .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
        .Hyperlinks.Add Anchor:=C, Address:="", SubAddress:="'" & C.Value & "'!A1"
    Next C
End With
End Sub

Hey this works great I'm just having a slight issue that you might be able to help me with? So I tried to adjust it so that It wouldn't touch the first 4 sheets on my workbook (the main sheet, header template, sheet template and item database) and I took out adding the hyperlink in to the other sheets (we had something like that already) but now it posts " '!A1 " into the first two cells on the first column in my master sheet. Any ideas as to why?

 

[Jetx227]

Here's my code if it helps:

Sub AddHyperLink_Click()


Dim C As Range
Dim i As Long
Dim ans As String
ans = "Estimate" 'Modify this name if needed all sheet names will be put in Column "A" of this sheet
Sheets(ans).Activate
Sheets(ans).Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).row).ClearContents
    
For i = 4 To Sheets.Count
    Sheets(ans).Cells(i, 1).Value = Sheets(i).Name
    Next


With Sheets(ans)
    For Each C In .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).row)
        .Hyperlinks.Add Anchor:=C, Address:="", SubAddress:="'" & C.Value & "'!A1"
    Next C
End With


End Sub

 

[My Aswer Is This]

I'm not really sure what your wanting now.
I just gave you a way to have a Hyperlink to each sheet.
But then you said you had some already
But then you said I see it works Ok now.
But now in the script your posting here you have made all sorts of modifications which is good.
It's good to see you know how to modify scripts.

But I'm not sure if your still needing help or just showing me what you now have.

 

[Jetx227]

I'm not really sure what your wanting now.
I just gave you a way to have a Hyperlink to each sheet.
But then you said you had some already
But then you said I see it works Ok now.
But now in the script your posting here you have made all sorts of modifications which is good.
It's good to see you know how to modify scripts.

But I'm not sure if your still needing help or just showing me what you now have.

I asked a question, then forgot to post my code in that same post and made a new post with it. I'll post both of them again here to avoid anymore confusion.

Hey this works great I'm just having a slight issue that you might be able to help me with? So I tried to adjust it so that It wouldn't touch the first 4 sheets on my workbook (the main sheet, header template, sheet template and item database) and I took out adding the hyperlink in to the other sheets (we had something like that already) but now it posts " '!A1 " into the first two cells on the first column in my master sheet. Any ideas as to why?

Here's my code if it helps:

Sub AddHyperLink_Click()


Dim C As Range
Dim i As Long
Dim ans As String
ans = "Estimate" 'Modify this name if needed all sheet names will be put in Column "A" of this sheet
Sheets(ans).Activate
Sheets(ans).Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).row).ClearContents
    
For i = 4 To Sheets.Count
    Sheets(ans).Cells(i, 1).Value = Sheets(i).Name
    Next


With Sheets(ans)
    For Each C In .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).row)
        .Hyperlinks.Add Anchor:=C, Address:="", SubAddress:="'" & C.Value & "'!A1"
    Next C
End With


End Sub

 

[Jetx227]

Let me just clarify a bit more too: I made a very simple macro on the sheets that made the master sheet the active one again so I took out the part of the code that added a hyperlink in cell "A1" to all the other sheets in the workbook. (BTW if I'm still not making any sense, which I wouldn't be surprised, I suck at explaining things over writing, but let me know and I can try to explain it another way).

 

[My Aswer Is This]

Try this:

Now see my plan was to write a script which you could run every time you added or deleted new sheets.
But now since your manually entering the links on your sheets some other way then as you add more sheets you will have to run this script and then manually enter the links on all your new sheets manually like I assume you at now doing. Not sure why you wanted to do it manually but then you must have your reasons.​

Sub New_One()
'Modified  7/12/2018  10:18:27 AM  EDT
Dim C As Range
Dim i As Long
Dim ans As String
ans = "Estimate" 'Modify this name if needed all sheet names will be put in Column "A" of this sheet
Sheets(ans).Activate
Sheets(ans).Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).ClearContents
    
For i = 4 To Sheets.Count
    Sheets(ans).Cells(i - 2, 1).Value = Sheets(i).Name
    Next

With Sheets(ans)
    For Each C In .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
        .Hyperlinks.Add Anchor:=C, Address:="", SubAddress:="'" & C.Value & "'!A1"
    Next C
End With

End Sub

 

[My Aswer Is This]

Ok.

Let me just clarify a bit more too: I made a very simple macro on the sheets that made the master sheet the active one again so I took out the part of the code that added a hyperlink in cell "A1" to all the other sheets in the workbook

 

[Jetx227]

Let me just completely rephrase my last question. So I used the code you gave me to make the hyperlinking on the master sheet of my workbook. I changed around a few things because on the other sheets (not the master sheet, the ones that the master sheet were hyperlinking to), the code you gave me inserted a hyper link in cell "A1" back to the master sheet, which I didn't need because the other sheets already had a form button that functioned the same way. I also adjusted the code so that it wouldn't look at the first 4 sheets of my workbook (the master sheet and 3 others that I didn't want hyperlinked). But when i made this change, in the first column on my master sheet where it printed the names of the sheets that it is hyperlinking to, the first 3 rows came up with " '!A1 " instead of a hyperlinked title. I was wondering if you could help explain why this was happening. Sorry for not explaining this thoroughly before, I really appreciate all the help.