Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to avoid duplicates across multiple worsheets in excel?
Ho do I avoid making duplicate entries across a number of worksheets in the
same spreadsheet? I can avoid duplicates on the same page by using the Data Validation function, but when I try to do this across more than one sheet I am not able to. Any help would be much appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to avoid duplicates across multiple worsheets in excel?
You can use the following macro linked to the Workbook_SheetChange event on the ThisWorkbook tab. It's a bit slow and can be improved by restricting the search range to the cells that you are likely to use. e.g. Sheet.Range(Sheet.Cells(1, 1), Sheet.Cells(100, 100)) instead of Sheet.Cells Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim DuplicatedValue As Boolean DuplicatedValue = False For Each Sheet In Sheets If Sheet Is Sh Then If Application.CountIf(Sheet.Cells, Target) 1 Then DuplicatedValue = True End If Else If Application.CountIf(Sheet.Cells, Target) 0 Then DuplicatedValue = True End If End If Next Sheet If DuplicatedValue = True Then MsgBox "Duplicate" End Sub -- mrice Research Scientist with many years of spreadsheet development experience ------------------------------------------------------------------------ mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931 View this thread: http://www.excelforum.com/showthread...hreadid=544326 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple instances of Excel | Excel Discussion (Misc queries) | |||
how to copy multiple Excel files from Outlook into Excel??? | Excel Discussion (Misc queries) | |||
Can I embed or link multiple .pdf files into or to an excel file? | Excel Discussion (Misc queries) | |||
Excel gets subtotals out of order using multiple sorts and subtot. | Excel Discussion (Misc queries) | |||
opening multiple instances of excel | Excel Discussion (Misc queries) |