Adding Sort To Current Code

[Pacman52]

Hi all, I have the following sub that populates a WS called 'Contents' with all the sheets in the workbook. Whilst testing with the end user they asked if the sheets listed could be sorted from oldest to newest.

The current and subsequent sheets in the workbook will all be in the format of '01-01-24 - Name1' (any new sheet names are created dependant on what userform is selected will always be in that format)

Could anyone let me know if its possible to adapt this code below to include the sort function?

Many thanks Paul

Sub Contents()

    Const showHidden As Boolean = False
    Dim wsList As Worksheet
    Dim sheetIndex As Integer
    Dim rowNumber As Integer
    Dim colNumber As Integer
    Dim sheetCounter As Integer
   
    On Error Resume Next

    Set wsList = ThisWorkbook.Sheets("Contents")
    On Error GoTo 0
    
    If wsList Is Nothing Then
        Set wsList = ThisWorkbook.Sheets.Add(Before:=ThisWorkbook.Sheets(2))
        wsList.Name = "Contents"
   
        Rows("2").RowHeight = 25
        Rows("3").RowHeight = 5
        Rows("5:500").RowHeight = 18
        Columns("B").ColumnWidth = 70

        ActiveWindow.DisplayGridlines = False
        ActiveWindow.DisplayHeadings = False
        
    Else
   
        wsList.Cells.Clear
       
    End If
    
    With wsList.Range("B2")
   
        .Value = "Contents"
        .Font.Bold = True
        .Font.Color = RGB(21, 96, 130)
        .Font.Name = "Calibri"
        .Font.Size = 20
        .Font.Underline = True
        .HorizontalAlignment = xlCenter
   
    End With
   
    With wsList.Range("B4")
   
        .Value = "Click once on the link below of the event you want to view."
        .Font.Bold = True
        .Font.Color = RGB(21, 96, 130)
        .Font.Name = "Calibri"
        .Font.Size = 12
        .Font.Italic = True
        .HorizontalAlignment = xlCenter

    End With
   
        rowNumber = 6
        colNumber = 2
 
 For sheetIndex = 1 To ThisWorkbook.Sheets.Count
       
        With ThisWorkbook.Sheets(sheetIndex)

            If .Name <> "Contents" And _
                (showHidden Or .Visible = xlSheetVisible) Then
                
                    wsList.Hyperlinks.Add _
                        Anchor:=wsList.Cells(rowNumber, colNumber), _
                        Address:="", _
                        SubAddress:="'" & _
                            ThisWorkbook.Sheets(sheetIndex).Name & _
                            "'!A1", _
                        TextToDisplay:=ThisWorkbook.Sheets(sheetIndex).Name
                    
                    rowNumber = rowNumber + 1
                    
            End If
       
        End With
       
    Next sheetIndex
   
End Sub

 

[Micron]

I can't tell if you're looking for a tab sort order (and if so, maybe from left to right) or sorting data on the sheets. I think it's the former.

EDIT -really seems this is about tab (sheet name) order. Maybe this will guide you, but a few name examples would help if it doesn't.

Sort Worksheets Alphanumerically by Name

Office VBA reference topic

learn.microsoft.com

 

[Pacman52]

I can't tell if you're looking for a tab sort order (and if so, maybe from left to right) or sorting data on the sheets. I think it's the former.

Hi sorry I should have been clearer,

What I'm after is to sort the list on the 'Contents' worksheet from initially 'B6' down to the last row populated. If it helps more to explain I'm happy to post a screenshot of the worksheet.

I've tried adding a new sub and calling it when the worksheet activates but it kept erroring out on the class. I don't know if this is because the sheet names in the actual list contain the 'date' element or not

Sub Sort ()
Dim LastRow As Long 
 
LastRow = Cells(Rows.Count, "B").End(xlUp).Row    
Range("B6:B" & LastRow).Sort Key1:=Range("B6"), Order1:=xlAscending,

End sub

 

[Joe4]

Never choose the name of a reserved word as the name of your procedures, functions or variables!
Doing so can cause errors and unexpected results.

You have named your procedure "Sort", and then you are trying to is the "Sort" function in it here:

Range("B6:B" & LastRow).Sort Key1:=Range("B6"), Order1:=xlAscending,

The problem is when you do that, whenever you call "Sort" in your procedure, VBA cannot be sure if you are trying to call your procedure named "Sort", or you are trying to invoke the Sort functionality.

Rename your function to something like "MySort".

Also, in that line of code above, you have a "," at the end with nothing after it. So you appear to be missing some code.

 

[Pacman52]

Never choose the name of a reserved word as the name of your procedures, functions or variables!
Doing so can cause errors and unexpected results.

You have named your procedure "Sort", and then you are trying to is the "Sort" function in it here:

Range("B6:B" & LastRow).Sort Key1:=Range("B6"), Order1:=xlAscending,

The problem is when you do that, whenever you call "Sort" in your procedure, VBA cannot be sure if you are trying to call your procedure named "Sort", or you are trying to invoke the Sort functionality.

Rename your function to something like "MySort".

Also, in that line of code above, you have a "," at the end with nothing after it. So you appear to be missing some code.

Hi Joe thanks for your reply,

The code in my previous response was quickly written on here just as an example hence the name and lack of indenting but duly noted anyway.

I’ve actually already tried the code you’ve included in your reply although I can’t remember if I called it or included it in my original sub. Could I ask what would be the best method to utilise it as it should work as it is

Thanks paul

 

[Joe4]

I’ve actually already tried the code you’ve included in your reply although I can’t remember if I called it or included it in my original sub.

Not sure what you mean. I just posted the line of code you had in your post that contains issues. It is an incomplete line of code. I am guessing you copied it from somewhere and didn't realize it spilled to multiple lines, and you only copied the first one. If you try to compile that code, you will get an error on that line.

So, it looks like you want to sort the Contents sheet starting on row 6 down to the last row in column B with data, right?
Do you have header/title row?
If so, is that line 5 or 6?
Just trying to figure out if line 6 should really included in the sort, or if it is the header/title row for your data.

 

[Joe4]

Also, is it only a one-column range you want to sort (column B)?
If it is a multi-column range, which columns are to be included in the sort?

 

[dmt32]

Hi
After removing the comma you have at the end of xlAscending your code as published ran ok.

Sub Sort ()
  Dim LastRow As Long
 
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row   
  Range("B6:B" & LastRow).Sort Key1:=Range("B6"), Order1:=xlAscending,
End sub

Just a point of view & for clarity, although point well made about using built in function, procedure names etc as names within your application as this can can cause conflicts that you would need to manage, it does not mean that you absolutely cannot use any of them -Sort is not a Keywords (reserved) word & your procedure once corrected would compile (I used it myself to test).

You will find that trying to use actual reserved words like Stop

Sub Stop

End Sub

the compiler will scream at you turning the word red & that's about as far as you will get.

If you are unsure how to manage potential name conflicts then best avoid at the outset & follow suggested guidance by changing the name slightly like mySort

Hope helpful

Dave

 

[Pacman52]

Hi Joe and Dave, thank you both so much for your replies and advice.

I have tried the code that Dave sent in his last post which did work, but it highlighted another issue for me being the sheet name structure of 'dd-mm-yyyy - WhateverText'. (Apart from having to change the range from B6 to B7 which was a error on my part as B6 references the Menu worksheet that I always want at the top of the list).

When the sort is called, it sorts the list by the 1st value of the sheet name and doesn't see it as a date. Is there a way to adapt the sort code to sort by dd-mm-yyyy and then by the text part after this ?

Thanks again Paul

 

[Joe4]

That is because your values are not dates (since they have text in them), so they will sort alphabetically.
Any change you could change the date portion of your string to a format of yyyy-mm-dd?
If so, then sorting alphabetically will also sort in date order, so it would sort the way you want then.