Stop macro if duplicate record found

puxovuya · Dec 3rd 2020

Hello

I have created this basic macro to copy data from one worksheet ("Search") to another ("Stocktake") and was wondering if anyone had advice on how to add code to get the macro to check if the values in column D ("Search") are already in column A ("Stocktake).

If they are the macro would stop and display a message "Asset already counted"

Code

Sub Macro27()
'
' Macro27 Macro
'
' Keyboard Shortcut: Ctrl+t
'
 Range("D10:O17").Select
 Selection.Copy
 Sheets("Stocktake").Select
 Range("A1").Select
 Range(Selection, Selection.End(xlDown)).Select
 Selection.End(xlDown).Select
 ActiveCell.Offset(1, 0).Range("A1").Select
 ActiveSheet.Paste
 Sheets("Search").Select
 Range("D3").Select
End Sub

Display More

**royUK** · Dec 3rd 2020

This code will copy the data then remove any duplicate rows

Code

Sub Macro27()
'
' Macro27 Macro
'
' Keyboard Shortcut: Ctrl+t
With Sheets("Stocktake")
Range("D10:O17").Copy .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
.Range("A1").CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlYes
End With
End Sub

Stop macro if duplicate record found

Participate now!

Share