PDA

View Full Version : Solved: Copy based on multiple condition



yogin
03-15-2011, 09:53 PM
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
03-16-2011, 01:17 AM
Brute force approach



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

yogin
03-16-2011, 03:18 AM
sorry nothing is happening...

GTO
03-16-2011, 03:32 AM
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
03-16-2011, 03:36 AM
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?

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:D" & 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

GTO
03-16-2011, 05:21 AM
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
03-16-2011, 05:28 AM
Thanks perfect...

mdmackillop
03-16-2011, 01:38 PM
Hi Yogin,
When you post code, please tag it using the green VBA button to format it as shown.

yogin
03-18-2011, 02:56 AM
hi mdmackillop,...sorry this is the first time i had put the code in...still learning VB.

Bob Phillips
03-18-2011, 03:15 AM
That's not VB yogin, that is forum facilities :)

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