Advanced Filtering VBA Code Issue

K

kc_native

Board Regular
Joined
Jan 20, 2009
Messages
176
I am trying to do an advanced filter in VBA and the code structure came from a macro I recorded, with some changes to range references. I am getting the following error: "Run-time error '1004': Application-defined or object-defined error. I would appreciate if someone could help me identify where in the code I have gone wrong. In the previous section of the code I am creating a sheet two and renaming it Target Projects. Do I need to add that new sheet name to the "CopyToRange=Range(A1)" portion of the code? The Target Project sheet is activated after adding it and re-naming it, before this section of the code executes.

Code: 
[B][COLOR=#0000FF]Sheets("DATA80100").Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, DataMoCol)) _
        .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Workbooks("Macro Launch Sheet).xlsm") _
        .Sheets("Sheet1").Range("BA1:BA36"), CopyToRange:=Range("A1"), Unique:=False[/COLOR][/B]

Thanks for any help you can give this novice.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
I don't really understand your criteria range, Its usually a heading on the table you are working on

Range you are looking at. advanced filter action, criteria (usually a column in the table),
copytorange​
 
Upvote 0
Does this work any better?
Code: 
With Sheets("DATA80100")
    .Range(.Cells(1, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, DataMoCol)) _
        .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Workbooks("Macro Launch Sheet).xlsm") _
        .Sheets("Sheet1").Range("BA1:BA36"), CopyToRange:=Sheets("Target Project").Range("A1"), Unique:=False
End With
 
Upvote 0
Norie said:
Does this work any better?
Code: 
With Sheets("DATA80100")
    .Range(.Cells(1, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, DataMoCol)) _
        .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Workbooks("Macro Launch Sheet).xlsm") _
        .Sheets("Sheet1").Range("BA1:BA36"), CopyToRange:=Sheets("Target Project").Range("A1"), Unique:=False
End With
Click to expand...

Hi, I tried adding the extra "." before Cells and the name of the copy to worksheet as you suggested, but it errors at where you added the first "." before the first "Cells". I am not sure why this code doesn't work since it was taken directly from the macro record script. Then only thing I changed was the range to extract the data from (With Sheets("DATA80100").Range.....) from a static range (e.g. - "A1:T10,000"), to one I am defining based on column and row numbers I am defining using the previously defined DataMoCol and the Cells(Rows.Count, 1).End(xlUp).Row command to always find the row number of the last line of data. Even if I put a static range in there instead, I get the same error.
 
Upvote 0
Could it be that I am trying to go to another worksheet to grab the criteria from? Maybe I need to copy the range of project numbers which are my criteria into the worksheet I am filtering from before I run the advance filter portion of the code, and then define the criteria range as a specified location in that sheet?
 
Upvote 0
I think this is not correct
CriteriaRange:=Workbooks("Macro Launch Sheet).xlsm")

Maybe something like
CriteriaRange:=Workbooks("worbook_name.xlsm").Sheets("sheet_name").Range("somerange")

M.
 
Upvote 0
Do you have unique headers for each column, any blank rows in the data, is there a header in the criteria range? When I get that error for AdvancedFilter, its often those kind of issue.
 
Upvote 0
Yeah, sorry. The original code was structured correctly in that area, I just inadvertently left in that ")" when deleting a persons name for privacy reasons, which was part of the Macro Launch file name. That's not what is causing the code to blow up. Can it be that I am trying to access criteria from a range in an external worksheet ("Macro Launch Sheet.xlsm") that is causing the problem?
 
Upvote 0
kc_native said:
Yeah, sorry. The original code was structured correctly in that area, I just inadvertently left in that ")" when deleting a persons name for privacy reasons, which was part of the Macro Launch file name. That's not what is causing the code to blow up. Can it be that I am trying to access criteria from a range in an external worksheet ("Macro Launch Sheet.xlsm") that is causing the problem?
Click to expand...

I'm not sure if the criteria range in another workbook is the culprit (not tested) but, for sure, you must specify the location of the criteria range in such workbook (sheet and range)

M.
 
Upvote 0
mikerickson said:
Do you have unique headers for each column, any blank rows in the data, is there a header in the criteria range? When I get that error for AdvancedFilter, its often those kind of issue.
Click to expand...

There are headers in all columns of the range of data I am pulling from and no blank rows, but no header in the criteria range, which is in a completely separate workbook. Could it be that I am trying to access criteria from a range in an external worksheet ("Macro Launch Sheet.xlsm") that is causing the problem?
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Advanced Filter Macro works until I run a different Advanced Filter Macro in same workbook
Replies
1
Views
70
rdarrylhudson
R
A
  • Question
Advanced Filter Macro with subtotal?
Replies
0
Views
96
Add564656
A
J
  • Solved
Advanced Filter using a date as the criteria
Replies
5
Views
323
Alex Blakenburg
A
M
  • Question
VBA Help - Advanced Filtering
Replies
1
Views
571
DanteAmor
DanteAmor
M
  • Solved
Data Advanced filter problem
Replies
2
Views
225
mikemcbain
M

Forum statistics

Threads
1,214,402
Messages
6,119,304
Members
448,886
Latest member
GBCTeacher

Share this page

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