Solved: Copy based on multiple condition

[yogin]

Is there a way I can copy data from one sheet to other when 3 conditions are met, out of which 2 are user input and third is completed field should be blank
I have a job sheet which has got a lot of column; however I need to get only 3 filed from this sheet to be copied into new sheet.

Can this be done, as currently I am using auto filter and copy paste manually.

[Bob Phillips]

Brute force approach

[vba]

Public Sub ProcessData()
Dim Lastrow As Long
Dim Nextrow As Long
Dim i As Long

Application.ScreenUpdating = False

With Worksheets("Job Sheet")

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Nextrow = 5
For i = 2 To Lastrow

If .Cells(i, "D").Value2 = Worksheets("Summary").Range("B2") And _
.Cells(i, "C").Value2 <= Worksheets("Summary").Range("C2") Then

Nextrow = Nextrow + 1
Worksheets("Summary").Cells(Nextrow, "A").Value2 = .Cells(i, "C").Value2
Worksheets("Summary").Cells(Nextrow, "B").Value2 = .Cells(i, "A").Value2
End If
Next i
End With

Application.ScreenUpdating = True
End Sub
[/vba]

[yogin]

sorry nothing is happening...

[GTO]

Hi there,

Reference: "These 2 filed (sic) are entered by user. The Date fill is the max date the report should show"

Is the 'max date' to reference the 'Start Date' or 'Due Date' column on the 'Job Sheet' worksheet?

[yogin]

thanks for looking into this, i have got the code do almost what I want except i want to clear the form every time i change Max date or employee id...hope you can help?

[VBA]Sub FindEmployee()

Dim SrchRows As Integer
Dim SrchRnge As String
Dim EID As String
Dim MyDate As Date

EID = Sheets("Summary").Range("B1")
MyDate = Sheets("Summary").Range("B2")
SrchRows = Sheets("Job Sheet").UsedRange.Rows.Count
SrchRnge = Sheets("Job Sheet").UsedRange.Address

If EID = "" Or MyDate = 0 Then Exit Sub

For Each c In Sheets("job sheet").Range("D2" & SrchRows)

If c = EID Then
If DateValue(MyDate) >= DateValue(Sheets("Job Sheet").Range(c.Address).Offset(0, -1)) Then
Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = _
Sheets("job sheet").Range(Cells(c.Row, c.Column).Address).Offset(0, -1)
Sheets("Summary").Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = _
Sheets("job sheet").Range(Cells(c.Row, c.Column).Address).Offset(0, -3)
Sheets("Summary").Range("E" & Rows.Count).End(xlUp).Offset(1, 0) = _
Sheets("job sheet").Range(Cells(c.Row, c.Column).Address).Offset(0, 5)
End If
End If

Next c

End Sub[/VBA]

[GTO]

thanks for looking into this, i have got the code do almost what I want except i want to clear the form every time i change Max date or employee id...hope you can help?

If I am understanding, right after...

If EID = "" Or MyDate = 0 Then Exit Sub

...try...

With ThisWorkbook.Worksheets("Summary")
    Range(.Cells(6, "A"), .Cells(.Rows.Count, "E")).ClearContents
End With

[yogin]

Thanks perfect...

[mdmackillop]

Hi Yogin,
When you post code, please tag it using the green VBA button to format it as shown.

[yogin]

hi mdmackillop,...sorry this is the first time i had put the code in...still learning VB.

[Bob Phillips]

That's not VB yogin, that is forum facilities

People like MD watch over so as to make it a better experience for all.