Message Pop Up

[CarlStephens]

Hello Everyone,

I have the below code that basically searches for required information on one sheet and then pastes it onto another sheet when it finds it, however, there arent always new information to paste across and I am wondering whether there is a code to add to the below that when there is no new information to copy and paste across that a message box will appear to say "No new employee records found" - is this possible? Thank you.

Sub CoypFilteredData()

Dim wsData      As Worksheet
Dim wsDest      As Worksheet
Dim lr          As Long

Application.ScreenUpdating = False

Set wsData = Worksheets("1. MAPS List")
Set wsDest = Worksheets("2. Joiners List")

wsData.Unprotect ("ML")


lr = wsData.Cells(Rows.Count, "AP").End(xlUp).Row

If wsData.FilterMode Then wsData.ShowAllData

With wsData.Rows(1)
    .AutoFilter Field:=42, Criteria1:="Not On Joiners List"
    .AutoFilter Field:=43, Criteria1:="<90"
    If wsData.Range("H1:H" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
        wsData.Range("AR2:AU" & lr).SpecialCells(xlCellTypeVisible).copy
        wsDest.Range("AC" & Rows.Count).End(3)(2).PasteSpecial Paste:=xlPasteValues
        wsDest.Select
    End If
    .AutoFilter Field:=42
    .AutoFilter Field:=43
    Range("AP1").Select
wsData.EnableAutoFilter = True
wsData.Protect Password:="ML", UserInterfaceOnly:=True
End With
Application.ScreenUpdating = True
End Sub

 

[Fluff]

If it's not finding the last row correctly on the destination sheet, it means you have data/formula in col AC below what you think is the last row.
Does it filter the data correctly?

 

[CarlStephens]

It filters correctly on the wsData tab....but when I look at the area that the code has copied, I see that it has copied blank rows.

 

[Fluff]

In that case check that the variable lr is correctly finding the last row.

 

[CarlStephens]

Thank you for all your advice, I think it is fixed now.

Last query, and I will leave you alone I promise lol. In the below code highlighted bold, there is in column AQ a possibility that there could be a "Yes" in the cell. Is there a code possible that will say in addition to the new employee records found (in red below), that code will count the number of "Yes" and pop up in the same message box as "new employee records found"?

With wsData.Rows(1)
.AutoFilter Field:=42, Criteria1:="Not On Joiners List"
.AutoFilter Field:=43, Criteria1:="<90"
If wsData.Range("H1:H" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
wsData.Range("AR2:AU" & lr).SpecialCells(xlCellTypeVisible).Copy
wsDest.Range("AC" & Rows.Count).End(3)(2).PasteSpecial Paste:=xlPasteValues
wsDest.Select
MsgBox wsData.Range("H1:H" & lr).SpecialCells(xlCellTypeVisible).Cells.Count - 1 & " new employee records were found."
Else
MsgBox "No new employee postings found."
End If

 

[Fluff]

As this is a different question, you will need to start a new thread. Thanks

 

[CarlStephens]

Will do, and thank you again, your support has saved me so much time....it is appreciated.