BlackTiger
New Member
- Joined
- Sep 18, 2014
- Messages
- 2
Good day,
Could anyone help me write a macro to find duplicate entries in an entire workbook.. in doing so can we isolate them via highlight. i've tried the following macro but it doesn't do what i want? Please help and Thank you in advance..
Sub myDups()
'Standard Module Code!
'Find All Duplicates on All Sheets!
Dim Message, Title, Default, SearchString
Message = "Enter your search string!" ' Set prompt.
Title = "Find ? On all sheets!" ' Set title.
Default = "" ' Set default.
' Display message, title, and default value.
SearchString = InputBox(Message, Title, Default)
Set S = Sheets.Application
myNext:
For Each S In Application.Sheets
With S.Range("A1:E700")
Set F = .Find(SearchString, MatchCase:=True, LookAt:=xlWhole, LookIn:=xlValues)
If Not F Is Nothing Then
firstAddress = F.Address
Do
S.Select
F.Select
ActiveCell.Offset(0, -1).Value = "Dup"
F.Value = SearchString
Set F = .FindNext(F)
Loop While Not F Is Nothing And F.Address <> firstAddress
End If
End With
Next S
End Sub
Could anyone help me write a macro to find duplicate entries in an entire workbook.. in doing so can we isolate them via highlight. i've tried the following macro but it doesn't do what i want? Please help and Thank you in advance..
Sub myDups()
'Standard Module Code!
'Find All Duplicates on All Sheets!
Dim Message, Title, Default, SearchString
Message = "Enter your search string!" ' Set prompt.
Title = "Find ? On all sheets!" ' Set title.
Default = "" ' Set default.
' Display message, title, and default value.
SearchString = InputBox(Message, Title, Default)
Set S = Sheets.Application
myNext:
For Each S In Application.Sheets
With S.Range("A1:E700")
Set F = .Find(SearchString, MatchCase:=True, LookAt:=xlWhole, LookIn:=xlValues)
If Not F Is Nothing Then
firstAddress = F.Address
Do
S.Select
F.Select
ActiveCell.Offset(0, -1).Value = "Dup"
F.Value = SearchString
Set F = .FindNext(F)
Loop While Not F Is Nothing And F.Address <> firstAddress
End If
End With
Next S
End Sub