Macro to copy and paste rows to a new sheet based on criteria

Hey guys,

I have a workbook in which i have lots of different sheets with the same formatting, what i am trying to do is have a macro automatically copy and paste rows from these sheets to another sheet named "overdue payments" if they match a certain criteria.

The criteria is in column K with the option of "Yes" or "No". I want the sheets to copy and paste if the answer is "No"

also i would like this to automatically update when the answer is changed.

I have attached a sample workbook for you to understand what i am talking about.

I would really appreciate some help as i am now really stuck! i know it can be done but I'm just not sure how to write up the macro!!

Thanks in advance

This is pretty straightforward, but your workbook is in a big state of "No" values right now. So I think the best solution is to simply have the OVERDUE sheet collect up all the "No" rows (based on the NOTES column K) each time you activate the OVERDUE sheet.

1) Right-click the OVERDUE sheet tab and select View Code
2) Paste in this sheet event code:

Option Explicit

Private Sub Worksheet_Activate()
Dim ws As Worksheet, LR As Long
Application.ScreenUpdating = False

If MsgBox("Update this sheet?", vbYesNo) = vbNo Then Exit Sub

Range("A3:K" & Rows.Count).Clear

For Each ws In Worksheets
    If ws.Name <> Me.Name Then
        ws.AutoFilterMode = False
        ws.Rows(2).AutoFilter
        ws.Rows(2).AutoFilter Field:=11, Criteria1:="No"
        LR = ws.Range("K" & ws.Rows.Count).End(xlUp).Row
        If LR > 2 Then
            ws.Range("A3:K" & LR).Copy
            Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
        End If
        ws.AutoFilterMode = False
    End If
Next ws

Application.ScreenUpdating = True
Range("A3").Select
End Sub

3) Exit the VBEditor and save as a macro-enabled workbook.
4) Now enter some "No" values in column K on the other sheets, then come back to the OVERDUE sheet and it will offer to update for you.

Not sure, it works. I had to put a bunch of "NO" answers in column K since there were none in your sample sheet.

Here's your sheet back, compare where I put the macro to where you did.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.