Ordering sheets based on a value in each cell

[Mrs_Sant0s]

Wonder if someone can help. I have a workbook that contains over 100 sheets. I would like to be able to order the sheets based on a value in a cell (D12) in each sheet, and I need the order to be highest to lowest.

Is there any VBCode that would allow me to create this?

Many thanks

 

[My Aswer Is This]

You said:
order the sheets based on a value in a cell (D12) in each sheet

What does order mean

Do you mean if D12 has 20 you want this sheet to now be sheet 20

 

[Mrs_Sant0s]

You said:
order the sheets based on a value in a cell (D12) in each sheet

What does order mean

Do you mean if D12 has 20 you want this sheet to now be sheet 20

Each sheet has a priority level in it (in cell D12) which ranges from 20 being high priority, to 1 being low priority. I would like all of the sheets that have a 20 priority in cell D12 to appear first, and all other sheets going down in number order level from there.

Thanks

 

[Tegg]

Voila! Hopefully, this is what you need

Sub Sort_Sheets()
Dim Sheet As Worksheet

' i = order priority
' loops through order priority
' if it's found in D12 then move to end
For i = 20 To 1 Step -1
For Each Sheet In Sheets

Sheet.Select
If Range("D12").Value = i Then
Sheet.Move after:=Sheets(Sheets.Count)
End If

Next Sheet
Next i


End Sub

 

[Mrs_Sant0s]

Voila! Hopefully, this is what you need

Sub Sort_Sheets()
Dim Sheet As Worksheet

' i = order priority
' loops through order priority
' if it's found in D12 then move to end
For i = 20 To 1 Step -1
For Each Sheet In Sheets

Sheet.Select
If Range("D12").Value = i Then
Sheet.Move after:=Sheets(Sheets.Count)
End If

Next Sheet
Next i


End Sub

Thank you Tegg! This kind of worked.... The only issue I now have is that I have a few sheets that I want it to ignore (called Risk Log, Blank, Lookup and Sheet1). When the macro gets to those sheets it errors, because there is other content D12 there that it doesn't like. Is there a way to tell it to ignore certain sheets that don't have a number value in D12? This is so close - thank you so much.

 

[Tegg]

Thank you Tegg! This kind of worked.... The only issue I now have is that I have a few sheets that I want it to ignore (called Risk Log, Blank, Lookup and Sheet1). When the macro gets to those sheets it errors, because there is other content D12 there that it doesn't like. Is there a way to tell it to ignore certain sheets that don't have a number value in D12? This is so close - thank you so much.

This will ignore sheets that don't have a number in cell D12, it should have ignored it anyways
Let me know how this gets on

Sub Sort_Sheets()
Dim Sheet As Worksheet

' i = order priority
' loops through order priority
' if it's found in D12 then move to end
For i = 20 To 1 Step -1
For Each Sheet In Sheets

Sheet.Select
If IsNumeric(Range("D12").Value) And Range("D12").Value = i Then
Sheet.Move after:=Sheets(Sheets.Count)
End If

Next Sheet
Next i


End Sub

 

[Mrs_Sant0s]

Hello Tegg. OK - This is working apart from anything that doesn't have a number is appearing first. So the sheets with Priority 20 are appearing after all of the blank sheets. Is there a way to the force the blank sheets to go to the end?

Thank you!

 

[Tegg]

Hello Tegg. OK - This is working apart from anything that doesn't have a number is appearing first. So the sheets with Priority 20 are appearing after all of the blank sheets. Is there a way to the force the blank sheets to go to the end?

Thank you!

I believe it's as simple as this
I've also made a few changes to formatting for easier reading and toggles screen updating so it should work faster

Hopefully this helps

Sub Sort_Sheets()
Application.ScreenUpdating = False
Dim Sheet As Worksheet

' i = order priority
' loops through order priority
' if it's found in D12 then move to end

For i = 20 To 1 Step -1
    For Each Sheet In Sheets
        Sheet.Select
    
        If IsNumeric(Range("D12").Value) And Range("D12").Value = i Then
            Sheet.Move after:=Sheets(Sheets.Count)
        End If
    Next Sheet
Next i

For Each Sheet In Sheets
    Sheet.Select
    If IsEmpty(Range("D12")) Then
        Sheet.Move after:=Sheets(Sheets.Count)
    End If
Next Sheet

Application.ScreenUpdating = True
End Sub

End Sub

 

[Mrs_Sant0s]

I believe it's as simple as this
I've also made a few changes to formatting for easier reading and toggles screen updating so it should work faster

Hopefully this helps

Sub Sort_Sheets()
Application.ScreenUpdating = False
Dim Sheet As Worksheet

' i = order priority
' loops through order priority
' if it's found in D12 then move to end

For i = 20 To 1 Step -1
    For Each Sheet In Sheets
        Sheet.Select
   
        If IsNumeric(Range("D12").Value) And Range("D12").Value = i Then
            Sheet.Move after:=Sheets(Sheets.Count)
        End If
    Next Sheet
Next i

For Each Sheet In Sheets
    Sheet.Select
    If IsEmpty(Range("D12")) Then
        Sheet.Move after:=Sheets(Sheets.Count)
    End If
Next Sheet

Application.ScreenUpdating = True
End Sub

End Sub

This is perfect - thank you. One final thing... once it has finished doing the sort it currently shows the sheet with the lowest priority level. Can you advise what the code would be to navigate to the sheet which is called "Risk Log" once the sort is complete? Thank you so much.

 

[Tegg]

This is perfect - thank you. One final thing... once it has finished doing the sort it currently shows the sheet with the lowest priority level. Can you advise what the code would be to navigate to the sheet which is called "Risk Log" once the sort is complete? Thank you so much.

Hello Again
Use this code, I just added Sheets("Risk Log").Select at the bottom

Sub Sort_Sheets()
Application.ScreenUpdating = False
Dim Sheet As Worksheet

' i = order priority
' loops through order priority
' if it's found in D12 then move to end

For i = 20 To 1 Step -1
    For Each Sheet In Sheets
        Sheet.Select
    
        If IsNumeric(Range("D12").Value) And Range("D12").Value = i Then
            Sheet.Move after:=Sheets(Sheets.Count)
        End If
    Next Sheet
Next i

For Each Sheet In Sheets
    Sheet.Select
    If IsEmpty(Range("D12")) Then
        Sheet.Move after:=Sheets(Sheets.Count)
    End If
Next Sheet

Application.ScreenUpdating = True
Sheets("Risk Log").Select
End Sub