Copying data to a new cell under a specific condition

[splendidmonkey]

Copying data to a new cell under a specific condition

I have column 1 which contains data
columns 2,3 and 4 will contain a flag (1)
if there is a flag in any of columns 2,3 and 4 i need
the data from column 1 on that row copied to column 1 on another sheet
to spice things up a bit some rows have a flag in 2&3, 2&4 and 2,3&4 etc

any ideas

 

[apeoortje]

Try:

Sub findit()
Range("A2").Select
start:
If ActiveCell.Value = "FLAG" Then
ActiveCell.EntireRow.Select
Selection.Copy
Workbooks.Open Filename:="filename.xls"
Sheets("Sheet1").Select
Rows("1:1").Select
Selection.Paste
Else
If ActiveCell.Value = "" Then
GoTo ende
Else
ActiveCell.Offset(1).Select
GoTo start
End If
End If
ende:
End Sub

 

[Loomah]

Hi
Another way, avoiding looping (which if you have thousands of rows of data could be tiresome) you could use autofilter.

I've assumed data to begin in column A and for flags to be in Columns B, C & D. I've assumed col E is empty as that is where I add a formula to identify flagged rows. It's all adaptable!

Sub a()
Dim lLastRow As Long
lLastRow = [a65536].End(xlUp).Row

Range("E1:E" & lLastRow).Formula = "=IF(COUNTA(B1:D1)<>0,""Copy"","""")"
Range("A1:E" & lLastRow).AutoFilter Field:=5, Criteria1:="Copy"
Range("A1").CurrentRegion.Columns(1).Copy _
    Worksheets(2).Range("A1")
Range("A1:E" & lLastRow).AutoFilter
Range("E1:E" & lLastRow).ClearContents
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[DaleWatson123321]

Hi Splendid,

I believe Loomah is on the right track, but I'd suggest going a step farther, and use the "Advanced Filter" capability of Excel. This will permit the extraction of data to a separate sheet, as you specified.

I've created an example file for you based on the information you provided. At the click of a button, the data from the first field is extracted to a separate sheet (based on whether the 2nd, 3rd or 4th fields contain a "1"). If you'd like the file, email me and I'll send it via return email.

I hope this helps.

Regards,

Dale Watson dalwatson@gov.mb.ca