Sorting Sheets by Date

[kl88]

Hello whoever reads this !

Im having a slight problem concerning a macro i have to make at work.

The goal is to select dates from a data tabel and making new sheets out of this. I suceeded into making that, it now makes new sheets with the month and year. Which is great and all, but i cant seem to sort them.
now march 2010 for example is before april 2011.
Is there any way i could sort all the sheets ?? (except for sheet1 which should always remain first)

I hope someone out here can help !

 

[baitmaster]

welcome to the board

please can you post your code (using the code tags as well)

Its probably best to sort your data table (either in the table itself, or within VBA), prior to sheet creation. Seeing what your working with, should help dictate how best to do this

 

[kl88]

Hey! Thanks for your reply

This is the code i have so FAR

Sub DatesWorksheetsByMonth()
'Turn of filters
If ActiveSheet.AutoFilterMode Then
    If ActiveSheet.FilterMode Then
        ActiveSheet.ShowAllData
    End If
ElseIf ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
End If
'Declare variables
    
    Dim rCell As Range, rRange As Range
    Dim wsTest As Worksheet
    Dim strMonth As String
    Range("B1:X1").Select
    Selection.Copy
    On Error Resume Next 'Prevent Run Time Errors
    'Set rRange to the last used cell in Column "A"
    Range("O1").End(xlDown).Select
    Selection.Offset(1, 0).Select
    Set rRange = Range(ActiveCell, Cells(Rows.Count, 1).End(xlUp))
    For Each rCell In rRange 'Loop from all cells in rRange Collection. rCell represent each cell
        If IsDate(rCell) Then 'Check if cell house a date
            strMonth = Format(rCell, "Mmmm yyyy") 'Pass the month name of the date
            Set wsTest = Nothing 'Ensure wsTest is reset to Nothing (default of Object variable)
            Set wsTest = Sheets(strMonth) 'Test to sheet if that Month name Sheet exists
            'Nothing means the sheet does not yet exist, so create it
            If wsTest Is Nothing Then Sheets.Add(After:=Sheets(1)).Name = strMonth
        End If
    Next rCell
    
    Call Sorteer2009
    Call Sorteer2010
    Call Sorteer2011
    Call Sorteer2012
    Call Sorteer2013
    
End Sub

With the CallSorteer it transfers al the files into the tabs, i think that it wouldnt make any difference putting it here.

Thanks for your help !

 

[baitmaster]

so if I understand this code right, it's running through the list of dates (which might not be in order), checking if a tab exists for that month yet, and if not creates it; Sorteer macros are just for transferring data into each sheet, its the sub you've detailed below that actually works with the workbook structure



If so, I'd look to reorder the list of dates, before using them to create worksheets. There is a problem though if some sheets already exist prior to this macro being run. Is the [look for existing sheet name] bit there because you have sheets already in place before this macro, or because duplicates would be made by this macro?

If the former, then I would try to create a macro for reorganising worksheets alphabetically, but this is complicated because the sheet names are text and not dates.... hopefully its the latter?

 

[kl88]

Yea, the code runs thru a list of dates , which arent in order because different onces keep being added. Sorteer macro's indeed just transfers data and the code i put here is the one working with the workbooks ^^.

It runs thru a lists of dates (mm/dd/yyyy). And it will create a new sheet for any month(year) that isnt in the sheets yet, the ones that are there he will skip over. The sheets are unfortunaltly text. Such as May 2011, June 2011 and so on.

Maybe i could everytime delete all the excists tabs and then run the macro so it has to make new ones everytime. But i would rather have it add new data everytime then totally write it over.