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!

 

[Norie]

Try this, it's a variant on code you'll find elsewhere.

Sub SortSheetsNumerically()
Dim I As Long, J As Long

    Application.ScreenUpdating = False
    
    For I = 3 To Sheets.Count - 1
        For J = I + 1 To Sheets.Count

            ' use Val to ensure sorting is numerical
            If Val(Sheets(J).Name) < Val(Sheets(I).Name) Then
                Sheets(J).Move before:=Sheets(I)
            End If

        Next J
    Next I
    
    Application.ScreenUpdating = True
    
End Sub

 

[Jetx227]

Try this, it's a variant on code you'll find elsewhere.

Sub SortSheetsNumerically()
Dim I As Long, J As Long

    Application.ScreenUpdating = False
    
    For I = 3 To Sheets.Count - 1
        For J = I + 1 To Sheets.Count

            ' use Val to ensure sorting is numerical
            If Val(Sheets(J).Name) < Val(Sheets(I).Name) Then
                Sheets(J).Move before:=Sheets(I)
            End If

        Next J
    Next I
    
    Application.ScreenUpdating = True
    
End Sub

This worked, thank you!!

 

[Jetx227]

Try this, it's a variant on code you'll find elsewhere.

Sub SortSheetsNumerically()
Dim I As Long, J As Long

    Application.ScreenUpdating = False
    
    For I = 3 To Sheets.Count - 1
        For J = I + 1 To Sheets.Count

            ' use Val to ensure sorting is numerical
            If Val(Sheets(J).Name) < Val(Sheets(I).Name) Then
                Sheets(J).Move before:=Sheets(I)
            End If

        Next J
    Next I
    
    Application.ScreenUpdating = True
    
End Sub

Wait one more question. So it sorts it perfectly, but now when I create the sheets it creates a sheet for the first column too, which it didn't before (it's like the column header so I excluded that row from the code) but now it keeps making a sheet with the header.

 

[Norie]

Not sure what you mean.

All that code does is sort the sheets numerically by their name excluding the first 2 sheets, nothing else.

 

[Jetx227]

Never mind I got it! I must've hit something on another line while putting in the code you gave me lol, thank you again!

 

[My Aswer Is This]

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

 

[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

This hyperlinking was actually one of my other posts too that haven't been answered yet. Is there a way to modify this so that if the first column on my "master" sheet already has the sheet names that I want, I can just add the hyperlink to the sheets from there? To clarify: I made a macro that takes the item numbers in the first column on my "master" sheet and creates a new sheet for each one. Can I add something like this code to make it so those cells can also hyperlink to their perspective sheets? And if I have cells that already have sheets made for them, can the code loop through and check if those cells are hyperlinked? Thanks!

 

[Jetx227]

Also, if it would be easier, I can just post my code and you can edit it to show me? I just registered to this forum today (and started VBA coding yesterday) so I'm very new to all this lol, so I don't know how to post code.

 

[My Aswer Is This]

Not sure I understand. My script if you test it clears out all the values in column A of the master sheet.
And then enters all the sheet names into column A of the master sheet with a link to that sheet.
I know of no easy way to look at every sheet name already in column A and see if there is a link already and if so skip that row.

Not sure why you would think that would be needed.

This script could be run again any time you add new sheets or delete sheets so as to update the sheet names and the links.