copy entire row if phrase is present

[fearfour]

Hi there, I am hoping that someone can help me out, I would really appreciate it! (Please note that I searched through the forum for an answer and found something sort of similar but not close enough, at least not for me to alter it at my skill level!)

Here's my situation and what I need to do:

In several worksheets (all within the same workbook), I have a red flag column in which either the phrase "Price too High" or "Price too low" appears. If the price is not too outlandish, then nothing appears in this column. In each of the worksheets, the red flag column is always in column R.

I am trying to look through each of the worksheets and if either of these phrases is present, I would like to copy the Entire Row into a worksheet called "Bid Analysis Summary".

The worksheets in which I would like to look contain varying numbers of rows of data, some are hundreds of rows long, some only have a few rows.

Although I am quite skilled with IF forlumas and such, my knowledge of VLOOKUP and the like is limited.

I can do this either with a macro or a formula....I just want to make it happen at this point as I am hitting the wall on this project!

Thank you so much for your help, you guys are great

 

[fearfour]

Whoops!

Sorry about that, I meant PGC!

 

[chicagodiceman]

fearfour,

Look at the solution posted. It should work with a bit of modification. You said that you had data for 12 columns. Change the example from A:D to A:L and that would pick up all twelve columns.

And the criteria can be changed to look for two values as follows (Credit pgc01 for original code):

Sub CopyUnpaid() 
Dim wksSumm As Worksheet, wks As Worksheet, rCopyFrom As Range, i As Integer 
    
Set wksSumm = Worksheets("Summary") 
wksSumm.UsedRange.ClearContents 
wksSumm.Range("A1:L1").Value = Worksheets(1).Range("A1:L1").Value 
For i = 1 To 3 
    Set wks = Worksheets(i) 
    wks.Columns("L").AutoFilter Field:=12, Criteria1:="=paid", operator:=xlOr, _
        Criteria2:="=unpaid"    
Set rCopyFrom = wks.Range("A2:L" & wks.Range("L" & wks.Rows.Count).End(xlUp).Row) 
    If rCopyFrom.Row <> 1 Then _ 
        rCopyFrom.SpecialCells(xlCellTypeVisible).Copy Destination:= _ 
            wksSumm.Range("A" & wksSumm.Range("L" & Rows.Count).End(xlUp).Row).Offset(1) 
    wks.AutoFilterMode = False 
Next 
End Sub

 

[Superstar31]

fearfour,

Look at the solution posted. It should work with a bit of modification. You said that you had data for 12 columns. Change the example from A:D to A:L and that would pick up all twelve columns.

And the criteria can be changed to look for two values as follows (Credit pgc01 for original code):

Sub CopyUnpaid() 
Dim wksSumm As Worksheet, wks As Worksheet, rCopyFrom As Range, i As Integer 
    
Set wksSumm = Worksheets("Summary") 
wksSumm.UsedRange.ClearContents 
wksSumm.Range("A1:L1").Value = Worksheets(1).Range("A1:L1").Value 
For i = 1 To 3 
    Set wks = Worksheets(i) 
    wks.Columns("L").AutoFilter Field:=12, Criteria1:="=paid", operator:=xlOr, _
        Criteria2:="=unpaid"    
Set rCopyFrom = wks.Range("A2:L" & wks.Range("L" & wks.Rows.Count).End(xlUp).Row) 
    If rCopyFrom.Row <> 1 Then _ 
        rCopyFrom.SpecialCells(xlCellTypeVisible).Copy Destination:= _ 
            wksSumm.Range("A" & wksSumm.Range("L" & Rows.Count).End(xlUp).Row).Offset(1) 
    wks.AutoFilterMode = False 
Next 
End Sub

Hmm that looks good, but here is my problem....

in column 5 will have RT and column 9 will have 1234

and it needs to be an "AND" not OR

Sheets("Data").Select
Columns("E:G").Select
Selection.Delete Shift:=xlToLeft
Range("A1:I1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=9, Criteria1:="1234", Operator:=xlAnd, Criteria2:="RT"
Cells.Select
Selection.Copy
Sheets("1234").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Data").Select
Selection.AutoFilter Field:=9, Criteria1:="2345"
Selection.AutoFilter Field:=5
Application.CutCopyMode = False
Selection.Copy
Sheets("2345").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1:I1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Cells.Select
Selection.ColumnWidth = 44
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit
Selection.AutoFilter Field:=5, Criteria1:="RT"
Sheets("1234").Select
Cells.Select
Selection.ColumnWidth = 38.57
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit
Sheets("Main").Select
End Sub