All,
Sorry if this has been answered before; I didn't know quite what to search for.
I am using VBA to copy a range based on filter results from a drop-down list. My problem is, if the there are no results from the drop-down list, the copy returns the ENTIRE range when I should think it would return nothing.
For example: a drop down list contains 3 choices, Cat1, Cat2, and Cat3. All row data is tagged to one of those choices. I use VBA to filter on that column and return a report that lists of all data in the different Categories, as such:
|___CAT 1___|___CAT 2___|___CAT 3___|
However, if all my data is in either CAT2 or CAT3 and nothing is tagged as CAT1, then the entire dataset is returned in the CAT 1 column on the results page.
Below is the code I was using:
Notes:
Main datasheet is "MyMainSheet" and table is "MyDataTable"
The 13th field is the drop-down list (Cat 1, Cat 2, Cat 3)
There are 199 rows of data starting on row 2
Results are copied to "MyResultSheet" and begin on Row 2
****Begin code****
Sheets("MyMainSheet").Select
ActiveSheet.ListObjects("MyDataTable").Range.AutoFilter Field:=13, Criteria1:= _
"CAT 1"
Range("A2:A200").Select
Selection.Copy
Sheets("MyReportSheet").Select
Range("A2").Select
ActiveSheet.Paste
****End code****
In the above example, if no row is tagged as Cat 1, the results returned are the entire data range in column A (as if no filter had been applied) instead of a blank column A on the results page.
What am I missing?
Thanks in advance for any suggestions.
Sand
Sorry if this has been answered before; I didn't know quite what to search for.
I am using VBA to copy a range based on filter results from a drop-down list. My problem is, if the there are no results from the drop-down list, the copy returns the ENTIRE range when I should think it would return nothing.
For example: a drop down list contains 3 choices, Cat1, Cat2, and Cat3. All row data is tagged to one of those choices. I use VBA to filter on that column and return a report that lists of all data in the different Categories, as such:
|___CAT 1___|___CAT 2___|___CAT 3___|
However, if all my data is in either CAT2 or CAT3 and nothing is tagged as CAT1, then the entire dataset is returned in the CAT 1 column on the results page.
Below is the code I was using:
Notes:
Main datasheet is "MyMainSheet" and table is "MyDataTable"
The 13th field is the drop-down list (Cat 1, Cat 2, Cat 3)
There are 199 rows of data starting on row 2
Results are copied to "MyResultSheet" and begin on Row 2
****Begin code****
Sheets("MyMainSheet").Select
ActiveSheet.ListObjects("MyDataTable").Range.AutoFilter Field:=13, Criteria1:= _
"CAT 1"
Range("A2:A200").Select
Selection.Copy
Sheets("MyReportSheet").Select
Range("A2").Select
ActiveSheet.Paste
****End code****
In the above example, if no row is tagged as Cat 1, the results returned are the entire data range in column A (as if no filter had been applied) instead of a blank column A on the results page.
What am I missing?
Thanks in advance for any suggestions.
Sand