Copy a range based on a filtered (drop-down list)

[Sand]

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

[Sand]

Results are copied to "MyResultSheet" and begin on Row 2

****Begin code****
Sheets("MyReportSheet").Select
****End code****

Obviously, "MyResultSheet" in the notes should be "MyReportSheet" as in the code. Sorry for the oversight.
Sand

[Bob Phillips]

Try this

Dim rng As Range

Sheets("MyMainSheet").Select
ActiveSheet.ListObjects("MyDataTable").Range.AutoFilter Field:=13, Criteria1:= _
"CAT 1"
On Error Resume Next
Set rng = Range("MyDataTable").SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.Copy Sheets("MyReportSheet").Range("A2")

[Jesse]

If you're pulling filtered data out of a list like this would it be better to filter and copy or step through the entries, load the matches in an array and write them to the output location?

Jesse

[Bob Phillips]

Correction

Dim rng As Range

Sheets("MyMainSheet").Select
ActiveSheet.ListObjects("MyDataTable").Range.AutoFilter Field:=13, Criteria1:= _
"CAT 1"
On Error Resume Next
Set rng = Range("MyDataTable").SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.Entirerow.Copy Sheets("MyReportSheet").Range("A2")

[Sand]

Thanks so much for the help!

Correction

On Error GoTo 0
If Not rng Is Nothing Then rng.Entirerow.Copy Sheets("MyReportSheet").Range("A2")

Your first "Not rng..." line works better. This one errors out because the copy range is not the same size and shape as the destination range. (I used the code with a destination that was not A2...)

Your first code did copy all the row data from the selection--if there was any data tagged as CAT 1--and did leave the column blank if there was not.

The "EntireRow.Copy" technique will be useful for me in another part of the workbook, I think. However, in the results table, I JUST need data from the one column ("A" in my example) to be displayed under each of the category headings. I'm sorry, I should have said.

Is there a modification that would allow me to only copy just the one column? Or, alternately, allow me to copy selected columns along the row?

Thanks again, I really appreciate the time you're spending teaching me!
Sand

[Bob Phillips]

Something like

rng.Entirerow.Columns("C:D")...

[Sand]

If you're pulling filtered data out of a list like this would it be better to filter and copy or step through the entries, load the matches in an array and write them to the output location?

Jesse

Thanks for the suggestion! I have played with arrays a little bit, and figured out how to display the contents of an array in a column using WorksheetFunction.Transpose(myArray), but I haven't figured out how to load the array with data that is dynamic; i.e., the number of units in each category could change from day to day.

Maybe this is what you are saying anyway, but couldn't I load the ENTIRE range into the array and then filter and display the results on the fly, per Category (by using For/Next loops to step through the array, for example)? If so, then I guess I would need to figure out how to load a two-dimensional array with both the category and the unit name columns, and then use For/Next to filter by Category, as such?

 
For x = LBound(MyArray) To UBound(MyArray)
    arFiltered = Filter(MyArray, "CAT1")
    Range("A1:A10").Select
    Range("A1:A10").Value = Application.WorksheetFunction.Transpose(arFiltered)
Next x

Something like that?

Now if I can only figure out how to load the proper column values into the array, and change the A1:A10 range to dynamic, I'll be all set. Maybe... ;-)
Sand

[Sand]

Something like

rng.Entirerow.Columns("C:D")...

That does work, thank you! I will test it more extensively to make sure, but looks like that might do the trick.

I am still going to try to figure out how to use an array, though, as a learning experience.

As an aside, why should I not just use your first code? What is wrong with NOT using the EntireRow parameter?

Thanks again for the help!
Sand

[Sand]

That does work, thank you! I will test it more extensively to make sure, but looks like that might do the trick.

Actually, not quite working yet. Still trying to figure out why. Doesn't seem to be copying all the visible data. It works with the first entry, but not all of them.

As an aside, why should I not just use your first code? What is wrong with NOT using the EntireRow parameter?

Delete that question for stupidity, sorry.