VBA - Filter Data based on criteria

[Reji Rajan]

Hi,

I am trying to make a report where the data pasted in sheet 1 is filtered as 2 criteria & the resulting data is pasted on sheet 2. I have managed to get data on sheet 2 with vba but only based on criteria 1. Sheet 1 has data from column A to Column N with the number of rows fluctuating. The conditions are as mentioned below:
criteria 1: If any cell in column M has text (have used IS TEXT)then the entire row gets highlighted with RED color.
criteria 2: if any cell in column A begins with "BB" (have used LEFT (A1),2), then the entire row gets highlighted with yellow color.

The resulting data with red & yellow color gets pasted in sheet 2.

Any advice or suggestions will help.

I have attached sample worksheet for reference.

Cheers!!
RJ

[mana]

you can use "advancedfilter"

[Reji Rajan]

Hi mana,

Extremely sorry for the late revert. I am new to VBA and rely a lot on google & recording macro to make a code. Honestly couldn't gather my head around "advancedfilter", but somehow managed to get the desired result. Anyways thank for the tip.

Cheers!
RJ

[Reji Rajan]

The solution: I placed a condition in column O, where i used an "istext" formula for column M colors the corresponding cell in column RED. Similarly, Used "LEFT" formula in column A (if the first 2 letters are BB, then color the cell YELLOW). Combined both the formulas (ISTEXT & LEFT), where if there was text, the result (in column O) will be TRUE & similarly if cell in column A began with BB, then the result (in column O) will be 1. Recorded a macro where i autofiltered with conditions, "TRUE" & "1", copied the selection & pasted the copied data in sheet2.

[mana]

Option Explicit

Sub test()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim rngC As Range
    
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")
    
    Set rngC = ws1.[P1:P2]
    rngC(2).Formula = "=OR(LEFT(A2,2)=""BB"",M2<>"""")"


    ws1.[A1].CurrentRegion.AdvancedFilter xlFilterCopy, rngC, ws2.[A1]
    rngC.ClearContents
    
End Sub

[Reji Rajan]

Wow...Thanks for the code, works well.

[YasserKhalil]

Hello Mr. Mana
I tested your code and found that it copied data to column A only in the target sheet ...
@Reji : Is that what you need ..I mean to get data from column A only?

[Reji Rajan]

Hey Yaseer, Thanks for pointing that out, i had pasted Mana's code in my existing code sheet, didn't realize it wasn't copying the data. When i copied the code to a new workbook, it copied the first 2 rows initially & then gave me a syntax error. Wondering whats wrong there. I want the code to copy data from Column A to F till the last active cell, though the filter is on column A only.

[mana]

>ws1.[A1].CurrentRegion.AdvancedFilter xlFilterCopy, rngC, ws2.[A1]

ws1.[A1].CurrentRegion.AdvancedFilter xlFilterCopy, rngC, ws2.[A1:N1]

[Reji Rajan]

Hi Mana,

You are a genius, i tried the code & works like a charm.thanks for the help.

Cheers!
RJ