Macro to extract data on multiple condition

[Rajender]

Hi All,

I am new to VBA . On basis of recording and online search I created a macro but it sometimes working and sometime not.

Can anybody suggest me where is the error ?

I have a sheets with name

1) Issues_Tracking sheet : - All the raw data(range from A565000)
2) Filter Sheet :- where I am apply a Advance filter macro with multiple condition(creteria mentioned in range : B2:E3 (with validation to select multiple option on heading " Domain, Country, Assigned to Owner, Issue Type) and same is linked to Issue Tracking sheet from range AO5:BD6)
Now In Issue Tracking Sheet Advance filter Criteria is from Ao5:BD6 also the cells which linked to Filter sheet contain the formula " =IF(Filter!B3="","",Filter!B3), =IF(Filter!C3="","",Filter!C3), =IF(Filter!D3="","",Filter!D3) & =IF(Filter!E3="","",Filter!E3).

Also the code which I used after is as mentioned below :-

Sub FilterData()
        Sheets("Issues_Tracking").Range("A5:P65000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
        Sheets("Issues_Tracking").Range("Ao5:BD6"), CopyToRange:=Sheets("Filter").Range("B6"), Unique:=False
    Columns.AutoFit
    Range("A1").Select
End Sub

Can any body suggest me why Code is not working ?

Regards,
Rajender

 

[Luke M]

Taking a guess here, but it might have to do with which sheet is active when you call it. Can you give more details as to "what's not working"? Code freezes, error message, code runs but no results...

Sub FilterData()
    'Can only copy data to Active sheet, so make sure
    'that Filter is active
    Worksheets("Filter").Select
    Sheets("Issues_Tracking").Range("A5:P65000").AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("Issues_Tracking").Range("AO5:BD6"), _
        CopyToRange:=Sheets("Filter").Range("B6"), _
        Unique:=False
    Columns.AutoFit
    Range("A1").Select
End Sub

 

[Rajender]

Taking a guess here, but it might have to do with which sheet is active when you call it. Can you give more details as to "what's not working"? Code freezes, error message, code runs but no results...

Sub FilterData()
    'Can only copy data to Active sheet, so make sure
    'that Filter is active
    Worksheets("Filter").Select
    Sheets("Issues_Tracking").Range("A5:P65000").AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("Issues_Tracking").Range("AO5:BD6"), _
        CopyToRange:=Sheets("Filter").Range("B6"), _
        Unique:=False
    Columns.AutoFit
    Range("A1").Select
End Sub

Hi Luke,

After running the macro nothing is showing. only few border lines are appearing in 1st column.
one more thing when i am changing range B6 to B6:Q65000 than result is showing. CopyToRange:=Sheets("Filter").Range("B6").

but i am confuse because I downloaded 1 excel file of VBA macro from this site when the code is mentioned same like my first post and all the data is showing. but here i need to mentioned range as B6:Q65000 .

Is there any specific reason or I am missing something ?

Regards,
Rajender

 

[Luke M]

Is there something already in B6?
or, you might be needing to specify at least B:Q so that it knows you want all columns transferred (as you can give only a few headers if you only want certain columns). What happens if you do:
CopyToRange:=Sheets("Filter").Range("B6:Q6")

 

[Rajender]

Is there something already in B6?
or, you might be needing to specify at least B:Q so that it knows you want all columns transferred (as you can give only a few headers if you only want certain columns). What happens if you do:
CopyToRange:=Sheets("Filter").Range("B6:Q6")

Hi Luke,

My active sheet is "Filter" and B6 contains the all the headers of raw data.
and i want to copy all the filtered data in sheet name "filtered".

Regards,
Rajender

 

[Luke M]

Would it be possible to post a sample workbook? I'm running out of ideas as to what is going wrong.

 

[Rajender]

Hi Luke,

I am facing error while uploading the document here. please find the below mentioned link.
https://drive.google.com/file/d/0B05F3hDqc3MoZFlpcGg4TVZRLTQ/edit?usp=sharing

Regards,
Rajender