VBA Advanced Filter

Isabella

Well-known Member
Joined
Nov 7, 2008
Messages
643
Hi, could someone please help me with this code, i am trying to copy visible cells to another sheet, but the code fails in line .offset

Code:
With shttest
.Range("Data").AdvancedFilter Action:=xlFilterCopy, 
CriteriaRange:=Range("Criteria")
        .Offset(1).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells (xlCellTypeVisible), _
        CopyToRange:=shtRec.Range("A1")
    End With
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Untested, but try:

<font face=Courier New><SPAN style="color:#00007F">With</SPAN> shttest.Range("Data")<br>    .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Criteria")<br>    .Offset(1).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells (xlCellTypeVisible), _<br>        CopyToRange:=shtRec.Range("A1")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN></FONT>

The code will still error if there are no visible cells after the filter.
 
Upvote 0
Untested, but try:

With shttest.Range("Data")
.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Criteria")
.Offset(1).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells (xlCellTypeVisible), _
CopyToRange:=shtRec.Range("A1")
End With


The code will still error if there are no visible cells after the filter.

Peter, i get an error "Named Argument not found"
CopyToRange:=

 
Upvote 0
Peter, i get an error "Named Argument not found"
CopyToRange:=

:oops: Yes, that's usually the result when you don't test, and don't read carefully enough.

If the code below does not do what you want, perhaps you better give us the rest of your code (so we know just what the variables you are using are) and explain exactly what you are trying to do.

<font face=Courier New><SPAN style="color:#00007F">With</SPAN> shttest.Range("Data")<br>    .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Criteria"), _<br>        CopyToRange:=shtRec.Range("A1")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>shtRec.Rows(1).Delete <SPAN style="color:#007F00">' Not sure if you want this line or not??</SPAN></FONT>
 
Upvote 0
:oops: Yes, that's usually the result when you don't test, and don't read carefully enough.

If the code below does not do what you want, perhaps you better give us the rest of your code (so we know just what the variables you are using are) and explain exactly what you are trying to do.

With shttest.Range("Data")
.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Criteria"), _
CopyToRange:=shtRec.Range("A1")
End With
shtRec.Rows(1).Delete ' Not sure if you want this line or not??

Peter this is the rest of my code, i am simply copying data that is in my criteria to another sheet


Code:
Sub Test()

Dim shtTestAs Worksheet
Dim shtRec As Worksheet

 
Set shttest = Sheets("test")
Set shtRec = Sheets("Rec")
 
With shtWorkings.Range("Data")
    .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Criteria")
    .Offset(1).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells (xlCellTypeVisible), _
    CopyToRange = shtRec.Range("A7")
End With
 
End Sub
 
Upvote 0
Peter this is the rest of my code, i am simply copying data that is in my criteria to another sheet


Code:
Sub Test()

Dim shtTestAs Worksheet
Dim shtRec As Worksheet

 
Set shttest = Sheets("test")
Set shtRec = Sheets("Rec")
 
With shtWorkings.Range("Data")
    .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Criteria")
    .Offset(1).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells (xlCellTypeVisible), _
    CopyToRange = shtRec.Range("A7")
End With
 
End Sub
So, do you now have the code doing what you want?

If not ..

a) Why do you have ...

Dim shtRec As Worksheet

Set shttest = Sheets("test")

... but then not use shttest in your code?

b) Why do you have ...

With shtWorkings.Range("Data")

... without 'Dim'ing or defining shtWorkings?

c) Did you try my suggested code?

d) Please confirm the name of the sheet your actual data is on and presumably contains the named ranges "Data" and "Criteria"?

e) Please confirm the name of the sheet you want your results to appear on?
 
Upvote 0
Peter your solution did not work, this is the full code, my data is in sheet test and i want the result to be copied to sheet Rec. When i run your solution the code only copies the header and the data in first column, everything else is missing.

Code:
Sub test()
 
Dim shttest As Worksheet
Dim shtRec As Worksheet
 
Set shttest = Sheets("test")
Set shtRec = Sheets("Rec")
 
With shttest.Range("Data")
    .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Criteria"), _
        CopyToRange:=shtRec.Range("A7")
End With
 
End Sub



So, do you now have the code doing what you want?

If not ..

a) Why do you have ...

Dim shtRec As Worksheet

Set shttest = Sheets("test")

... but then not use shttest in your code?

b) Why do you have ...

With shtWorkings.Range("Data")

... without 'Dim'ing or defining shtWorkings?

c) Did you try my suggested code?

d) Please confirm the name of the sheet your actual data is on and presumably contains the named ranges "Data" and "Criteria"?

e) Please confirm the name of the sheet you want your results to appear on?
 
Upvote 0
... this is the full code, my data is in sheet test and i want the result to be copied to sheet Rec ....

Code:
Sub test()
 
Dim shttest As Worksheet
Dim shtRec As Worksheet
 
Set shttest = Sheets("test")
Set shtRec = Sheets("Rec")
 
With shttest.Range("Data")
    .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Criteria"), _
        CopyToRange:=shtRec.Range("A7")
End With
 
End Sub
This code appears to work for me.

Here is my "test" sheet. The named range 'Data' is the green range. Te named range 'Criteria' is the blue range.

Excel Workbook
ABCDEFG
1H1H2H3H4H4
2awsdd
3ssas
4wew
5adwe
6sed
7
test




After running the code ...

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> test()<br>    <SPAN style="color:#00007F">Dim</SPAN> shttest <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> shtRec <SPAN style="color:#00007F">As</SPAN> Worksheet<br> <br>    <SPAN style="color:#00007F">Set</SPAN> shttest = Sheets("test")<br>    <SPAN style="color:#00007F">Set</SPAN> shtRec = Sheets("Rec")<br>     <br>    <SPAN style="color:#00007F">With</SPAN> shttest.Range("Data")<br>        .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("Criteria"), _<br>            CopyToRange:=shtRec.Range("A7")<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

... the "Rec" sheet looks as below. Is that what you would expect?


Excel Workbook
ABCDE
6
7H1H2H3H4
8awsd
9sed
10
Rec
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top