Issues with autofilters

[Tucker92]

I have been experimenting with auto filters and was wondering if there is a way that i could get it to copy the information to another workbook but don't know how. Can anyone help me?

Here is what i have so far but i don't know why it doesn't work. Hopefully what i'm trying to do is possible
and i havent done it all wrong.

Sub Copy_12()


Dim ws As Worksheet
     Set ws = Application.Workbooks("Carry Over").Sheets("Vac A")
     Dim data_end_row_number As Long
     data_end_row_number = ws.Range("A2").End(xlDown).Row
       
    ws.Range("table6").AutoFilter Field:=13, Criteria1:="12 WET", VisibleDropDown:=True
    ws.Range("A2:B" & data_end_row_number).SpecialCells(xlCellTypeVisible).copy
    Windows("Vac AC").Sheets("12 Wet").Range("D2").Select
        ActiveSheet.Paste
         ws.Range("D2:F" & data_end_row_number).SpecialCells(xlCellTypeVisible).copy
    Windows("Vac AC").Sheets("12 Wet").Range("D2").Select
    ActiveSheet.Paste

 

[RoryA]

What exactly does "doesn't work" mean?

Also, is this a different question to this one: https://www.mrexcel.com/forum/excel-questions/1077065-issue-filters-vba.html

Finally, please use code tags when posting code.

 

[Tucker92]

I have been trying out different approaches to similar problems, as im quite new to vba and am using these as learning resources.

And it wont allow me to copy to the other workbook and im not sure why. It filters and finds the results but wont let me copy them across so i think i have done something wrong but dont know what it is.

Sorry for not using the code tags i haven't used them before.

 

[RoryA]

"Won't let me" isn't particularly helpful either, I'm afraid. What is happening? Do you get an error, or does nothing happen, or does the wrong thing happen?

 

[Tucker92]

Sorry. I comes up with a run time error '438'. says the object doesn't support this property or method.

Not sure what i means but i thought it may have been to do with the copy location.

 

[RoryA]

Ah - a Window doesn't have a Sheets property. You need the Workbooks collection instead of Windows:

Sub Copy_12()


Dim ws As Worksheet
     Set ws = Application.Workbooks("Carry Over").Sheets("Vac A")
     Dim data_end_row_number As Long
     data_end_row_number = ws.Range("A2").End(xlDown).Row
       
    ws.Range("table6").AutoFilter Field:=13, Criteria1:="12 WET", VisibleDropDown:=True
    ws.Range("A2:B" & data_end_row_number).SpecialCells(xlCellTypeVisible).copy Destination:=Workbooks("Vac AC").Sheets("12 Wet").Range("D2")
         ws.Range("D2:F" & data_end_row_number).SpecialCells(xlCellTypeVisible).copy Destination:=Workbooks("Vac AC").Sheets("12 Wet").Range("D2")

As a side note, it is much safer to include the file extension when using the Workbooks collection.

 

[Tucker92]

Thank you.

I have tried it out and it works great.

Only issue i do have though is that if there are no cells present then it just comes up with a box saying there are no cells to copy but i want it to carry on until the end so that when i call that macro it will carry on to the next afterwards.

I tried to put in an "on error go to" but i'm not sure where to place it in my code. Is there an easy way to skip the copying if there are no filtered results?

I was going to try with an if but don't know how to use it with the auto filter.

 

[RoryA]

You could do something like this:

Dim ws As Worksheet
     Set ws = Application.Workbooks("Carry Over").Sheets("Vac A")
     Dim data_end_row_number As Long
     data_end_row_number = ws.Range("A2").End(xlDown).Row
       
    ws.Range("table6").AutoFilter Field:=13, Criteria1:="12 WET", VisibleDropDown:=True
    If ws.Range("table6").Range.Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 then
    ws.Range("A2:B" & data_end_row_number).SpecialCells(xlCellTypeVisible).copy Destination:=Workbooks("Vac AC").Sheets("12 Wet").Range("D2")
         ws.Range("D2:F" & data_end_row_number).SpecialCells(xlCellTypeVisible).copy Destination:=Workbooks("Vac AC").Sheets("12 Wet").Range("D2")
    end if

 

[Tucker92]

It comes up with a Compile error saying argument is not optional and highlights the second ".range" i tried to remove this and it comes up with "runtime error 1004 no cells found" again

 

[RoryA]

Sorry, I forgot to change one of them:

If ws.Listobjects("table6").Range.Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 then

if it's actually a table. If not, then just:

If ws.Range("table6").Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 then