I have code that ideally when clicked will filter by the name of the button and prompt a date range for the end user to choose a start date and the resulting "Results" tab will only have dates equal to or greater than it
Sub zzzFilterData()
Dim wsData As Worksheet
Dim wsResults As Worksheet
Dim strCaption As String
Dim lastRow As Long
Dim startDate As Date
Set wsData = ThisWorkbook.Worksheets("Sheet1")
'Prompt the user to enter a start date
startDate = InputBox("Enter a start date (format: MM/DD/YYYY)")
strCaption = ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text 'Get the caption of the button that was clicked
'Check if the results sheet exists and create it if it doesn't
On Error Resume Next
Set wsResults = ThisWorkbook.Worksheets("Results")
On Error GoTo 0
If wsResults Is Nothing Then
Set wsResults = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
wsResults.name = "Results"
End If
'Clear any existing data on the results sheet
wsResults.Cells.Clear
'Filter the data based on the button caption and start date
lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row 'Get the last row of data in column A
wsData.Range("A1").AutoFilter Field:=1, Criteria1:=strCaption, Operator:=xlAnd, Criteria2:=">=" & startDate 'Filter the data in column A based on the button caption and start date
'Copy the filtered data to the results sheet
wsData.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy wsResults.Range("A1")
'Remove the filter
wsData.Range("A1").AutoFilter
'Select the results sheet
wsResults.Activate
End Sub
the Results tab however only has the headers and then blank columns. I have tried changing the code to
lastRow = wsData.Cells(wsData.Rows.Count, "G").End(xlUp).Row
wsData.Range("G1").AutoFilter Field:=7
as that is where the date in the data is but the end product is the same. Does anyone have any insight?
Bookmarks