Hello!
I have spent hours looking online for an answer and I can't seem to find one that matches my exact situation, so I'm really hoping someone can please help. I have a pretty lengthy VBA code that is filtering data on one sheet, then copying and pasting the results onto another sheet. The two main parts of the filter are:
1. Filter all dates before or equal to today
2. Filter all date cells after today
The headers are in row 8. The range is dynamic, so I have some coding in the copy & paste to choose the next row down from row 8.
After each of those, I have code to copy and paste the results into a separate worksheet, copy that worksheet to a new book, then clear out the sheet in the original workbook.
The problem I'm encountering is if the filter returns no results, which will occur if there are only dates prior to today or only dates after today and not a combination of both.
Can someone please help me write in something to say if the autofilter returns no results, then do not copy and paste the data to a new sheet?
I hope that all makes sense, but please let me know if I need to clarify anything. I will be forever grateful to whoever can help. Thank you in advance!!
I have spent hours looking online for an answer and I can't seem to find one that matches my exact situation, so I'm really hoping someone can please help. I have a pretty lengthy VBA code that is filtering data on one sheet, then copying and pasting the results onto another sheet. The two main parts of the filter are:
1. Filter all dates before or equal to today
VBA Code:
Sheets("Quote Summary").Select
Worksheets("Quote Summary").Unprotect "oupusa"
If Not Worksheets("Quote Summary").Range("A8").AutoFilter Then
Worksheets("Quote Summary").Range("A8").AutoFilter
End If
ActiveSheet.Range("X8").AutoFilter Field:=24, Criteria1:= _
"<=" & Now(), Operator:=xlAnd
2. Filter all date cells after today
VBA Code:
Sheets("Quote Summary").Select
Worksheets("Quote Summary").Unprotect "oupusa"
If Not Worksheets("Quote Summary").Range("A8").AutoFilter Then
Worksheets("Quote Summary").Range("A8").AutoFilter
End If
ActiveSheet.Range("X8").AutoFilter Field:=24, Criteria1:= _
">" & Now(), Operator:=xlAnd
The headers are in row 8. The range is dynamic, so I have some coding in the copy & paste to choose the next row down from row 8.
VBA Code:
Sheets("Quote Summary").Select
Range("A8").Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Cells().Select
Selection.Copy
Sheets("Advantage Agreement Upload").Select
Range("H25").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
After each of those, I have code to copy and paste the results into a separate worksheet, copy that worksheet to a new book, then clear out the sheet in the original workbook.
The problem I'm encountering is if the filter returns no results, which will occur if there are only dates prior to today or only dates after today and not a combination of both.
Can someone please help me write in something to say if the autofilter returns no results, then do not copy and paste the data to a new sheet?
I hope that all makes sense, but please let me know if I need to clarify anything. I will be forever grateful to whoever can help. Thank you in advance!!