Copy/paste separate cells if 2 others match cells on another sheet. Loop process through all rows (variable number)

[chr1sj]

Hi all,

I have a workbook that essentially ends up with all the information on a helper sheet named "MeetingInfo". Other sheets in the workbook then pick and choose data from the MeetingInfo sheet to populate their cells. I have become stuck trying to code for one of the sheets to copy data only when certain criteria are met, please see details below.

worksheet meetinginfo has columns from a to bd and an unknown number of lines.
worksheet payroll has a data entry point in cell k3 for the month.

I would greatly appreciate some help with creating a macro that will copy selected cells when both of the following criteria are met:
1. The value of meetinginfo cell bd2 matches that of payroll cell k3
2. The value of meetinginfo cell an2 = "w" or "d"

When both criteria are met I would like it to copy the data from meetinginfo cells a2, b2 and an2 to payroll cells a3, b3 and c3 respectively. (Paste destination would need to be to 1st clear line which will always start at 3.)

I then need the macro to look at the next line of data in meetinginfo, check the same criteria and paste when necessary to the next available line in payroll. I would need the process to repeat until all lines in meetinginfo have been checked.

I hope this is clear and any help would be very much appreciated.

Kind regards,

Chris

 

[chr1sj]

This has been my attempt but is failing miserably:

Sub DidTheyWork()

Dim i As Integer
Dim copyS As Worksheet
Dim pasteS As Worksheet
Set copyS = Worksheets("MeetingInfo")
Set pasteS = Worksheets("Payroll")

copyS.Select
finalrow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To finalrow
thisvalue = Cells(i, 40).Value
If thisvalue = "w" Or thisvalue = "d" Then

copyS.Range("i, 1:i,2").Copy
pasteS.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
copyS.Select
copyS.Range("i, 40").Copy
pasteS.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False

End If
Next i

End Sub

Can anyone see an obvious error or suggest another way to do this?

Thanks in advance,

Chris

 

[chr1sj]

does anyone have any ideas on this please?