VBA copying rows from multiple worksheets based on 2 different criteria in 2 columns

[ChrisLB]

Hi,

I am looking for a VBA solution to copy data in the same format from 13 separate worksheets (months of the year 3 letter abbreviations: "Jan", "Feb" etc and "2014-15") across into a "High priority" worksheet.

The criteria I am looking for are either "high" found in column M OR "likely" found in column Y.

I have a code set up doing this from an "OPEN" tab (shown) however that is a bit of a middle man and need to ideally pull it directly from each month.

The destination worksheet "High priority" will keep the top row as the column headings which are the same across all worksheets. There are a few other worksheets in the document with different information that I don't want to touch.

Any help would be much appreciated, let me know if I've missed any useful information.

C


Sub HighPriorityOPEN()


Dim lr1 As Long, lr2 As Long, r As Long


lr1 = Sheets("OPEN").Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Sheets("High priority").Cells(Rows.Count, "A").End(xlUp).Row


For r = 2 To lr1 Step 1
If Range("M" & r).Value = "high" Or Range("Y" & r).Value = "likely" Then
Rows(r).Copy Destination:=Sheets("High priority").Range("A" & lr2 + 1)
lr2 = Sheets("High priority").Cells(Rows.Count, "A").End(xlUp).Row
End If
Next r


End Sub