Recall rows of data from an excel table - error

[Dancey_]

Hey guys,

Can someone please help me, I have the below code:

Sub RecallSales()

On Error GoTo ErrHandler

Application.ScreenUpdating = False

Dim MyCheck As Range

Workbooks.Open "\\GD-NAS02\Helpdesk\Renewal Retention Team\eConversion Sales Reporting\Sales\Database\Sales Database.xlsm"

For Each MyCheck In Workbooks("Sales Database.xlsm").Sheets("Sales").Range("C1:C1000")

If MyCheck.Value = ThisWorkbook.Sheets("Agent Daily Report").Range("B1").Value Then

Range(ActiveCell.Offset(-2, 0), ActiveCell.Offset(9, 0)).Select

Range(ActiveCell.Offset(-2, 0), ActiveCell.Offset(9, 0)).Copy

Workbooks("Sales Database.xlsm").Sheets("Sales").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues

Else: MsgBox "No sales recorded today"

Workbooks("Sales Database.xlsm").Close

Exit Sub

End If

Next MyCheck

Workbooks("Sales Database.xlsm").Close

ErrHandler:
    MsgBox "The message text of the error is: " & Error(Err)

End Sub

I have a excel table that is updated by a different sub and is a table of sales input by sales agents.

The idea of this sub is to allow the agent to select their name from a drop down in B1 and then it will pull all sales made by them and put them into a table on the same sheet.

My sub is jumping straight to the "no sales recorded" MsgBox. For some reason it isnt picking up that MyCheck.Value does indeed equal B1.Value. I have manually input an agents name instead of Range("B1").Value but it still doesn't work.

Please help!

 

[Andrew Poulsom]

It will execute the Else clause as soon as a match isn't found, and that could of course be the first item.