Sort rows in a workbook based on Date in Row A

This sounds very simple, however i have searched many forums and tried various codes but without sucess, my data spreads from Columns A to R and starts in row 4.

Would be grateful if someone could help me with the code to sort the data by column A

many thanks

Thanks Debraj for your prompt response. Does this mean i need a button as i was hoping for it to apply automatically upon opening.

Hi Debraj, i am receiving a Runtime error 9 subscript out of range, any ideas?

Try this,

Private Sub Workbook_Open()
    Dim lr As Long
    Dim mr As Long
    lr = Worksheets("Partial Shifts").Range("A" & Rows.Count).End(xlUp).Row
    mr = Worksheets("Day Off Trades").Range("A" & Rows.Count).End(xlUp).Row

    ActiveWorkbook.Worksheets("Partial Shifts").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Partial Shifts").Sort.SortFields.Add Key:=Range( _
        "A2:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Partial Shifts").Sort
        .SetRange Range("A1:L" & lr)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    ActiveWorkbook.Worksheets("Day Off Trades").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Day Off Trades").Sort.SortFields.Add Key:=Range( _
        "A2:A" & mr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Day Off Trades").Sort
        .SetRange Range("A1:L" & mr)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
End Sub

Hi thanks for responding.

I was googling this as we speak without any luck, I Assume I need to change "Partial Shifts" and "Day off Trades" are these the tab names? I have 12 tabs - January to February. Appologies for asking a silly question but i am new to this. My Worksheet is called Events Spreadsheet.

Really appreciate your help.

Yes "Partial Shifts" and  "Day off Trades" are worksheets name.
If you want 12 tab then you have to create 12 objects for each one.

Thank you so much, i have my header in Row 3 and it is placing the data above this, what part of the code do i need to ammend? really really appreciate your help.

Would you mind checking my code, i guessed based on what you gave me. It actually works fine, however i get an error message saying excel found unreadable content this appeared after i entered the code, if i click on yes this is from a trusted source its fine and sorts perfectly but obviously i have done something wrong?!

private Sub Workbook_Open()
    Dim lr As Long
    Dim mr As Long
    Dim PR As Long
    Dim QR As Long
    Dim RR As Long
    Dim SR As Long
    Dim TR As Long
    Dim UR As Long
    Dim VR As Long
    Dim XR As Long
    Dim YR As Long
    Dim ZR As Long
    
    lr = Worksheets("January").Range("A" & Rows.Count).End(xlUp).Row
    mr = Worksheets("February").Range("A" & Rows.Count).End(xlUp).Row
    PR = Worksheets("March").Range("A" & Rows.Count).End(xlUp).Row
    QR = Worksheets("April").Range("A" & Rows.Count).End(xlUp).Row
    RR = Worksheets("May").Range("A" & Rows.Count).End(xlUp).Row
    SR = Worksheets("June").Range("A" & Rows.Count).End(xlUp).Row
    TR = Worksheets("July").Range("A" & Rows.Count).End(xlUp).Row
    UR = Worksheets("August").Range("A" & Rows.Count).End(xlUp).Row
    VR = Worksheets("September").Range("A" & Rows.Count).End(xlUp).Row
    XR = Worksheets("October").Range("A" & Rows.Count).End(xlUp).Row
    YR = Worksheets("November").Range("A" & Rows.Count).End(xlUp).Row
    ZR = Worksheets("December").Range("A" & Rows.Count).End(xlUp).Row
    
    ActiveWorkbook.Worksheets("January").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("January").Sort.SortFields.Add Key:=Range( _
        "A2:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("January").Sort
        .SetRange Range("A1:R" & lr)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    ActiveWorkbook.Worksheets("February").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("February").Sort.SortFields.Add Key:=Range( _
        "A2:A" & mr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("February").Sort
        .SetRange Range("A1:R" & mr)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        
    End With
    ActiveWorkbook.Worksheets("March").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("March").Sort.SortFields.Add Key:=Range( _
        "A2:A" & PR), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("March").Sort
        .SetRange Range("A1:R" & PR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    ActiveWorkbook.Worksheets("April").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("April").Sort.SortFields.Add Key:=Range( _
        "A2:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("April").Sort
        .SetRange Range("A1:R" & QR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
     ActiveWorkbook.Worksheets("May").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("May").Sort.SortFields.Add Key:=Range( _
        "A2:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("May").Sort
        .SetRange Range("A1:R" & RR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
      
    ActiveWorkbook.Worksheets("June").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("June").Sort.SortFields.Add Key:=Range( _
        "A2:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("June").Sort
        .SetRange Range("A1:R" & SR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("July").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("July").Sort.SortFields.Add Key:=Range( _
        "A2:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("July").Sort
        .SetRange Range("A1:R" & TR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    ActiveWorkbook.Worksheets("August").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("August").Sort.SortFields.Add Key:=Range( _
        "A2:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("August").Sort
        .SetRange Range("A1:R" & UR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    ActiveWorkbook.Worksheets("September").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("September").Sort.SortFields.Add Key:=Range( _
        "A2:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("September").Sort
        .SetRange Range("A1:R" & VR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
                
    End With
    
    ActiveWorkbook.Worksheets("October").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("October").Sort.SortFields.Add Key:=Range( _
        "A2:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("October").Sort
        .SetRange Range("A1:R" & XR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    ActiveWorkbook.Worksheets("November").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("November").Sort.SortFields.Add Key:=Range( _
        "A2:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("November").Sort
        .SetRange Range("A1:R" & YR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
     ActiveWorkbook.Worksheets("December").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("December").Sort.SortFields.Add Key:=Range( _
        "A2:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("December").Sort
        .SetRange Range("A1:R" & ZR)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
End Sub

if you want to add worksheet automatically then use this code

Dim NewSheet As Worksheet 
Set NewSheet = Sheets.Add 
 
NewSheet.Name = "NewSheetName"

Hi thanks again for replying.

I am really sorry to ask but where do i insert the code and what part of my code do i need to remove, i really am very new to this and willing to learn.

You don't need to declare 12 object for 12 months saperately you just add the above code that I given to you that add worksheet automatically to your workbook.
That code you have to place within the loop.

Look at this sample code:

Function SectorSheet()

    Dim InputWorkBk As Workbook    
    Dim InpWS As String
    Dim InpCounter As Integer
    Dim EndRowS As Integer
    Dim InputSheet As String
    Dim InWS As String
    Dim OutCounter As String
    Dim InpCount As String
    
    InputSheet = "SectorTemplate"
    InpWS = "SectorDetails"
    InWS = "DrillDown_Attribution"
    
    InputWorkBk.Worksheets(InpWS).Select
    InputWorkBk.Worksheets(InpWS).Range("A2").Select
    Selection.End(xlDown).Select
    EndRowS = ActiveCell.Row
    
    InpCounter = 2
    
    
    Do Until InputWorkBk.Worksheets(InpWS).Range("A" & InpCounter).Value = ""
    
        If InputWorkBk.Worksheets(InpWS).Range("O" & InpCounter).Value = "1" Then
            
            NewSheetName = InputWorkBk.Worksheets(InpWS).Range("A" & InpCounter).Value
            InputWorkBk.Worksheets(InputSheet).Copy After:=Sheets(Sheets.Count)
            ActiveSheet.Name = NewSheetName
            
            InputWorkBk.Worksheets(NewSheetName).Range("A3").Value = NewSheetName
            
        End If
            
            InpCount = 9
            OutCounter = 3
            
    Do Until InputWorkBk.Worksheets(InWS).Range("A" & InpCount).Value = ""
        
        If InputWorkBk.Worksheets(InWS).Range("A" & InpCount).Value = NewSheetName Then
                
        If InputWorkBk.Worksheets(InWS).Range("B" & InpCount).Value <> "" Then
        
            If OutCounter < EndRowS Then
                
                InputWorkBk.Worksheets(NewSheetName).Range("O" & OutCounter).Value = InputWorkBk.Worksheets(InWS).Range("B" & InpCount).Value
                InputWorkBk.Worksheets(NewSheetName).Range("P" & OutCounter).Value = InputWorkBk.Worksheets(InWS).Range("J" & InpCount).Value
                InputWorkBk.Worksheets(NewSheetName).Range("Q" & OutCounter).Value = InputWorkBk.Worksheets(InWS).Range("K" & InpCount).Value
                InputWorkBk.Worksheets(NewSheetName).Range("R" & OutCounter).Value = InputWorkBk.Worksheets(InWS).Range("L" & InpCount).Value
                InputWorkBk.Worksheets(NewSheetName).Range("S" & OutCounter).Value = InputWorkBk.Worksheets(InWS).Range("H" & InpCount).Value
                
                OutCounter = OutCounter + 1
            
            End If
            
        End If
        
        End If
        
        InpCount = InpCount + 1
    
    Loop
            
        InpCounter = InpCounter + 1
    
    Loop

End Function

I hope you understand.

Hi, as i said i am a real newbie i cant read the code but i have been playing around wth it for an hour and a half. I have the following, i have been adapting your code to the sample code, am i on the right tracks??

Private Sub Workbook_Open()
    Dim lr As Long
    Dim mr As Long
    Dim NewSheet As Worksheet
    Set NewSheet = Sheets.Add
 
NewSheet.Name = "NewSheetName"
    
    
    Do Until Eventsspreadsheet.WorkBk = Worksheets("January").Range("A" & Rows.Count).End(xlUp).Row
    
   NewSheetName = Eventsspreadsheet.WorkBk.Worksheets("January").Range("A" & Rows.Count).End(xlUp).Row
            Eventsspreadsheetworkbk.Worksheets(February).Copy After:=Sheets(Sheets.Count)
            ActiveSheet.Name = February
            Eventsspreadsheetworkbk.Worksheets(February).Range("A" & Rows.Count).End(xlUp).Row = February
            
            End If
            
           Do Until Eventsspreadsheet.WorkBk = Worksheets("March").Range("A" & Rows.Count).End(xlUp).Row

If Eventsspreadsheet.WorkBk = Worksheets("March").Range("A" & Rows.Count).End(xlUp).Row = April Then

I think i need settig straight with what i have and then i will have another go, i am self teaching so do not understand the terminology of the code.

Thansk for helping me get my head round it. (Sorry reposted with code tags and not HTML!)

you done wrong man.

I attached image with this post.

check it.

code.JPG

Thanks for your reply and the code explanation. My code knowledge is very basic and therfore i am unable to interpret the information you are giving me. Im not sure what i need to change/add on your sample. I will have to leave it and manually sort my data on each tab as i really do not understand how to write it from what you have provided.

Thanks for your patience.

I have done it i fiddled around with what i was doing and it works, admittedly its probably not pretty but it does the job. Thansk so much for your patience and help.

Private Sub workbook_open()

 Range("A2:R215").Select
    ActiveWorkbook.Worksheets("January").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("January").sort.SortFields.Add Key:=Range("A2:A215" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("January").sort
        .SetRange Range("A2:R215")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
      Range("A2:R217").Select
    ActiveWorkbook.Worksheets("February").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("February").sort.SortFields.Add Key:=Range( _
        "A2:A217"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("February").sort
        .SetRange Range("A1:R217")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A2:R215").Select
    ActiveWorkbook.Worksheets("March").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("March").sort.SortFields.Add Key:=Range("A2:A215" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("March").sort
        .SetRange Range("A2:R215")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
        Range("A2:R217").Select
    ActiveWorkbook.Worksheets("April").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("April").sort.SortFields.Add Key:=Range( _
        "A2:A217"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("April").sort
        .SetRange Range("A1:R217")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A2:R215").Select
    ActiveWorkbook.Worksheets("May").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("May").sort.SortFields.Add Key:=Range("A2:A215" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("May").sort
        .SetRange Range("A2:R215")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
        Range("A2:R217").Select
    ActiveWorkbook.Worksheets("June").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("June").sort.SortFields.Add Key:=Range( _
        "A2:A217"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("June").sort
        .SetRange Range("A1:R217")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Range("A2:R215").Select
    ActiveWorkbook.Worksheets("July").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("July").sort.SortFields.Add Key:=Range("A2:A215" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("July").sort
        .SetRange Range("A2:R215")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
        Range("A2:R217").Select
    ActiveWorkbook.Worksheets("August").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("August").sort.SortFields.Add Key:=Range( _
        "A2:A217"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("August").sort
        .SetRange Range("A1:R217")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A2:R215").Select
    ActiveWorkbook.Worksheets("September").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("September").sort.SortFields.Add Key:=Range("A2:A215" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("September").sort
        .SetRange Range("A2:R215")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
       Range("A2:R217").Select
    ActiveWorkbook.Worksheets("October").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("October").sort.SortFields.Add Key:=Range( _
        "A2:A217"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("October").sort
        .SetRange Range("A1:R217")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A2:R215").Select
    ActiveWorkbook.Worksheets("November").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("November").sort.SortFields.Add Key:=Range("A2:A215" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("November").sort
        .SetRange Range("A2:R215")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
       Range("A2:R217").Select
    ActiveWorkbook.Worksheets("December").sort.SortFields.Clear
    ActiveWorkbook.Worksheets("December").sort.SortFields.Add Key:=Range( _
        "A2:A217"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("December").sort
        .SetRange Range("A1:R217")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
   End With
    
   End Sub

Anytime.

If you want more help then ask me