excel - selecting sheets dependant on date in sheet name

[Lee_S]

Hi,

I have a workbook that is made up of 13 sheets, named January to December and one called reports.

Now i have a sub on the report sheet that runs through all the other sheets and copies certain data to the reports sheet in a nice list.

But i want to add a sort of manual filter to the reports list by way of a combobox.

Options will be:
"All Months" - Copy relevant data from all sheets
"Previous Months" - Copy data from sheets PREVIOUS to system date
"Future Months" - Copy all data from sheets AFTER system date

Im sure you get the idea.

I figured the combobox_change event could fire the current sub, and then i'll have a check in there to see what the combo's value is. Any idea how i can incorporate that so it checks the date and matches it to the sheet names?

Here is the current sub.

Private Sub Worksheet_Activate()
Dim rng As Range
Dim lLastRow As Integer
Dim iNextRow As Integer
Dim x As Integer
Dim bFound As Boolean
 
Dim ws As Worksheet
Dim sht_Rpt As Worksheet  'Set a handle for the Reports Sheet
Set sht_Rpt = Sheets("Reports")
 
    sht_Rpt.Rows("7:65536").Delete Shift:=xlUp
    bFound = False
    For Each ws In Worksheets
        If ws.Name <> "Reports" Then
            Set rng = ws.Range("A1").SpecialCells(xlCellTypeLastCell)
            lLastRow = rng.Row
            
            For x = 2 To lLastRow 'First two rows contain headers etc
                If ws.Cells(x, "E").Value = "" And ws.Cells(x, "A").Value <> "" And ws.Cells(x, "E").Interior.ColorIndex = xlNone Then
                    If bFound = False Then
                        bFound = True
                        With sht_Rpt.Cells(FindBottomRow(sht_Rpt, 1), "A")
                            .Font.Bold = True
                            .Value = ws.Name
                        End With
                    End If
                    iNextRow = FindBottomRow(sht_Rpt, 1)
                    sht_Rpt.Range("A" & iNextRow, "D" & iNextRow).Value = ws.Range("A" & x, "D" & x).Value
                    sht_Rpt.Range("E" & iNextRow).Value = ws.Range("G" & x).Value
                End If
            Next x
        End If
        bFound = False
    Next
End Sub