Advanced Filtering VBA Code Issue

[kc_native]

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.

[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.

 

[Dryver14]

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​

 

[Norie]

Does this work any better?

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

 

[kc_native]

Does this work any better?

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

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.

 

[kc_native]

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?

 

[Marcelo Branco]

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.

 

[mikerickson]

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.

 

[kc_native]

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?

 

[Marcelo Branco]

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?

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.

 

[kc_native]

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.

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?