Excel Filtering for a Summary Tab

[ItalianPlatinum]

I have a 5k+ row sheet that contains Invoices, job Ids, Merchants, with Due Dates and Shipment dates among a lot of other unique data. I need a way to quickly and easily identify based off of year when something was shipped. I want to maintain one sheet lets call it data. Then be able to look at another sheet say (2024) or (2025) to reference right there automatically what has been shipped for that year. The sheet 2024 will have all the columns in the data tab that I populated. So essentially a summary filtered by Ship date of 2024. Would anyone be able to help? The user isn't very computer inclined so having them filter or advance filtering themselves is a no can do. I know Excel has FILTER and UNIQUE filter formulas that I think may work but I had issues getting them to work so looking for some help. Thank you in adcance!

Job #	Shipment Date	Due Date	A	B	C	D	X	X	X	X
1	1/1/2023	12/2/2022	AA	12	123	1234	X	X	X	X
2	1/1/2022	12/2/2021	BB	66	55	77	X	X	X	X
3	1/1/2024	12/2/2023	CC	99	88	24	X	X	X	X
4	10/1/2024	9/1/2024	DD	856	874	314	X	X	X	X
5	10/5/2020	9/5/2020	EE	101	202	303	X	X	X	X
6	9/9/2020	8/10/2020	FF	444	555	666	X	X	X	X
7	9/1/2024	8/2/2024	GG	888	999	110	X	X	X	X
8	11/1/2024	10/2/2024	HH	5687	45874	2365	X	X	X	X

 

[ItalianPlatinum]

Interesting that is kind of neat. Both work well.

 

[ItalianPlatinum]

@Fuji so your line of code is unhiding the data set then not rehiding after. Is there a modification that can be done so it doesn’t do that OR it identifies what’s hidden then hides it when done. I did create a new post for it.

VBA to detect hidden rows. then Re-hide

I have a line of code that transfers data over to a tab based off of data in cell A2. When doing so it un-hides my source data, and doesn't re-hide that source data after. I am looking for a way to detect what is hidden before the code runs, run the code, then reapply the hidden rows. Currently...

www.mrexcel.com

 

[ItalianPlatinum]

This pastes the data into the "Summary" sheet.

Sub FilterByYear()
    Dim filterYear As Long
    Dim ws As Worksheet, summarySheet As Worksheet
    Dim lastRow As Long
    Dim rng As Range
 
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set summarySheet = ThisWorkbook.Worksheets("Summary")

    filterYear = ws.Range("M2").Value
    lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
 
    ' Apply filter based on year
    ws.Range("A3:K" & lastRow).AutoFilter Field:=2, _
        Criteria1:=">=" & DateSerial(filterYear, 1, 1), _
        Operator:=xlAnd, _
        Criteria2:="<=" & DateSerial(filterYear, 12, 31)
 
    ' Check if there are visible rows
    On Error Resume Next
    Set rng = ws.Range("A4:K" & lastRow).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
 
    If Not rng Is Nothing Then
        ' Clear existing data on the Summary sheet
        summarySheet.Cells.Clear
     
        ' Copy filtered cells to the Summary sheet
        ws.Rows(3).Copy Destination:=summarySheet.Range("A1") ' Copy header
        rng.Copy Destination:=summarySheet.Range("A2")        ' Copy filtered data
    Else
        MsgBox "No data found for the specified year.", vbExclamation
    End If

    ws.AutoFilterMode = False
End Sub

@Cubist Is there a way for your code to not unhide the sheet1 rows? Or for it to rehide what was hidden before running the line of code?

 

[Cubist]

It's untested.

Sub FilterByYear()
    Dim filterYear As Long
    Dim ws As Worksheet, summarySheet As Worksheet
    Dim lastRow As Long
    Dim rng As Range
    Dim hiddenRange As Range
    Dim cell As Range

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set summarySheet = ThisWorkbook.Worksheets("Summary")

    filterYear = ws.Range("M2").Value
    lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row

    ' Record the range of hidden rows
    For Each cell In ws.Rows("4:" & lastRow).Columns(1).Cells
        If cell.EntireRow.Hidden Then
            If hiddenRange Is Nothing Then
                Set hiddenRange = cell.EntireRow
            Else
                Set hiddenRange = Union(hiddenRange, cell.EntireRow)
            End If
        End If
    Next cell

    ' Unhide all rows temporarily
    ws.Rows("4:" & lastRow).EntireRow.Hidden = False

    ' Apply filter based on year
    ws.Range("A3:K" & lastRow).AutoFilter Field:=2, _
        Criteria1:=">=" & DateSerial(filterYear, 1, 1), _
        Operator:=xlAnd, _
        Criteria2:="<=" & DateSerial(filterYear, 12, 31)

    ' Check if there are visible rows
    On Error Resume Next
    Set rng = ws.Range("A4:K" & lastRow).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If Not rng Is Nothing Then
        ' Clear existing data on the Summary sheet
        summarySheet.Cells.Clear

        ' Copy filtered cells to the Summary sheet
        ws.Rows(3).Copy Destination:=summarySheet.Range("A1") ' Copy header
        rng.Copy Destination:=summarySheet.Range("A2")        ' Copy filtered data
    Else
        MsgBox "No data found for the specified year.", vbExclamation
    End If

    ws.AutoFilterMode = False

    ' Re-hide rows that were originally hidden
    If Not hiddenRange Is Nothing Then
        hiddenRange.EntireRow.Hidden = True
    End If
End Sub

 

[ItalianPlatinum]

So as a test I hid rows 2-20. But now only 4-20 is hidden after running. Also, I am not sure if its a big change. But could a dialog box be used for the criteria asking the user? instead of inputting in the cell of M2. If that warrants a new post I can obviously do that after we situate the hidden rows. Thanks @Cubist

 

[ItalianPlatinum]

Actually I think you coded fine. Forgot my sample test I provided you guys started on row 4. but my actual data is on row 2. I just modified the Rows("4:" to Rows("2:" and seems to work.