Excel VBA - advanced filter

Question

0.00/5 (No votes)

See more:

Hi,

I have data from A1 to GC15000 on Sheet1; criteria set out on A1:A2 and try copy filtered data to Sheet 3 by pressing the command butotn on Sheet 2,,but I get run time error 1004 (The exact range is a missing or invalid fieldname)

When I try setting the data range and copy to range from A1: Z15000, it works..

Thank you

What I have tried:

Sheets("Sheet1").Range("A1:GC15000").AdvancedFilter Action:=xlFilterCopy, _
 CriteriaRange:=Sheets("Sheet2").Range("A1:A2"), CopyToRange:=Sheets("Sheet3").Range("A1:GC15000"), Unique:=False

Posted 19-Feb-17 2:09am

Member 12982752

Updated 20-Feb-17 2:58am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2017-02-19T03:08:00

Solution 1

Please, read this MSDN article[^] to find out what error 1004 means.

Such of addressing: Sheets("Sheet2") may not be enough. Imagine, you have 2 workbooks opened. Each of workbook contains Sheet2. Does the VBA compiler is able to know what sheet you mean? No!

So, replace this:
VB

Sheets("Sheet1").Range("A1:GC15000").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Sheet2").Range("A1:A2"), CopyToRange:=Sheets("Sheet3").Range("A1:GC15000"), Unique:=False

with:

VB

Sub CopyData()
Dim srcwsh As Worksheet, criwsh As Worksheet, dstwsh As Worksheet

On Error Goto Err_CopyData

Set srcwsh = ThisWorkbook.Worksheets("Sheet1")
Set criwsh = ThisWorkbook.Worksheets("Sheet2")
Set dstwsh = ThisWorkbook.Worksheets("Sheet3")

srcwsh.Range("A1:GC15000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=criwsh.Range("A1:A2"), CopyToRange:=dstwsh.Range("A1"), Unique:=False

Exit_CopyData:
    Set srcwsh = Nothing
    Set criwsh = Nothing
    Set dstwsh = Nothing
    Exit Sub

Err_CopyData:
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_CopyData
End Sub

For futher details, please see:
Excel VBA Performance Coding Best Practices - Office Blogs[^]
Handle Run-Time Errors in VBA[^]
Excel 2010 Performance: Tips for Optimizing Performance Obstructions[^]

Posted 19-Feb-17 3:08am

Maciej Los

Updated 19-Feb-17 3:50am

v4

Comments

Member 12982752 19-Feb-17 9:43am

Thank you.. No errors now but it copies only upto Z.. I have column upto GC.. I need this filter to copy upto column GC.. Thanks

Maciej Los 19-Feb-17 9:52am

Seems, i forgot to add a range to srcwsh. Please check my answer after changes. If it meets your criteria, please accept it (green button).

Member 12982752 19-Feb-17 10:19am

Thank you.. it perfectly works.. :)

Maciej Los 19-Feb-17 12:31pm

You're very welcome

Member 12982752 19-Feb-17 10:23am

I have one more question..I would like to eliminatie the record which has no date ( if there is no date for the record; i.e :- I have date for some records on DF column, just filter only them just eliminate the empty ones)

Thanks

Maciej Los 19-Feb-17 12:30pm

You have to add another field in criteria and leave it empty, then modify code ;)

Member 12982752 19-Feb-17 13:29pm

Thanks for the reply but apologies.. It should be otherway around,, I need to keep the empty ones and eleminate date..

Thank you

Maciej Los 19-Feb-17 15:11pm

So, in a cell A3 of Sheet2 insert <> as criteria.
More at: Advance Filter to Display Rows with Non-Blank Values

Member 12982752 20-Feb-17 8:59am

I have blank and "Yes" on the column.. Just would like to keep blanks and remove Yes.. I tried ="" and <> both filter out the blank and return Yes.. Please help :)

Maciej Los 20-Feb-17 9:56am

Sorry, i don't see your data, so i can't help you. Keep trying!

Member 12982752 20-Feb-17 10:32am

Submitted Withdrawn
Yes

Yes

Yes

Yes

It is something like this.. The column heading is Submitted Withdrawn.. so the colummn is filled with either yes or blank..