Sort by Date

[trevormay99]

I have a macro that moves data for the upcoming week to the top of selected spreadsheets, the code is as follows

   Dim currentDate         As Date
    Dim upcomingWeekEnd     As Date
    Dim ArrayRow            As Long
    Dim HelperColumnNumber  As Long
    
    Dim DateColumnArray     As Variant, HelperColumnArray   As Variant
    Dim sheetNames          As Variant
   
'
    currentDate = Date                                                                      ' Save today's date to currentDate
    upcomingWeekEnd = currentDate + 7                                                       ' Save the date that is 7 days from today to upcomingWeekEnd
'
    ' Loop through all worksheets in the workbook
    For Each ws In ThisWorkbook.Worksheets                                                  ' Loop through the sheets in the workbook
        Select Case ws.Name
            Case Is = "Prod Meet", "Elec Meet", "Mech Meet"                                 '       If the sheet name matches a sheet that we are looking for then ...
                With ws
                    lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row                        '               Get the lastRow of the date column
                    .Range("A1").AutoFilter                                                 '               Turn on the filter
                    .Range("A1:L" & lastRow).Sort Key1:=.Range("F1"), Order1:=xlAscending, _
                            Header:=xlYes                                                   '               Filter the rows of data ascending according to the dates in column F
                    .AutoFilterMode = False                                                 '               Turn off the filter
'
                    HelperColumnNumber = .Cells.Find(What:="*", LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 2 '             Get HelperColumnNumber to use
                    DateColumnArray = .Range("F2:F" & lastRow).Value2                       '               Save the dates from column F into DateColumnArray
                End With
'
                ReDim HelperColumnArray(1 To UBound(DateColumnArray), 1 To 1)               '           Establish the dimensions of the HelperColumnArray
'
                For ArrayRow = 1 To UBound(DateColumnArray)                                 '           Loop thru rows of DateColumnArray
                    If DateColumnArray(ArrayRow, 1) >= currentDate And _
                            DateColumnArray(ArrayRow, 1) <= upcomingWeekEnd Then            '               If the date is within the date range then ...
                        HelperColumnArray(ArrayRow, 1) = 1                                  '                   Save '1' to the HelperColumnArray
                    End If
                Next                                                                        '           Loop back
'
                With ws.Range("A2").Resize(UBound(DateColumnArray), HelperColumnNumber)     '
                    .Columns(HelperColumnNumber).Value = HelperColumnArray                  '               Write the HelperColumnArray to the HelperColumnNumber
                    .Sort Key1:=.Columns(HelperColumnNumber), Order1:=xlAscending, _
                            Header:=xlNo                                                    '               Sort the Rows with '1's to the top
                    .Columns(HelperColumnNumber).Delete                                     '               Delete the HelperColumn
                End With
        End Select
    Next

However I also have a labor hour summation next to my data table, when this code runs and moves the rows of data that fit the timeframe to the top of the sheet it pushes the hour summation down is there any way to prevent this from happening? As it makes the sheet look messy.

 

[Peter_SSs]

Duplicate to: Sort by Date

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.