Sub FindDuplicates()
Dim SheetName As String
Dim Target As Range

Set Target = Application.InputBox("Select any cell on the target sheet
with the mouse", Type:=8)
If Not Target Is Nothing Then

SheetName = Target.Parent.Name
With Sheets("Current Month")

.Range("R2").FormulaR1C1 = _
"=IF(COUNTIF('" & SheetName &
"'!C[-14],RC[-14])=1,""Y"","""")"
.Range("R2").AutoFill Destination:=.Range("R2:R2000"),
Type:=xlFillDefaultSelect
.Columns("R:R").Copy
.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
.Range("R3").Select
End With
Application.CutCopyMode = False
End If
End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"NPell" wrote in message
...
This is my macro..

Sub FindDuplicates()

Sheets("Current Month").Select
Range("R2").Select

ActiveCell.FormulaR1C1 = _
"=IF(COUNTIF(Duplicates!C[-14],RC[-14])=1,""Y"","""")"
Range("R2").Select
Selection.AutoFill Destination:=Range("R2:R2000"),
Type:=xlFillDefault
Range("R2:R2000").Select

Sheets("Current Month").Select
Columns("R:R").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("R3").Select
End Sub



It works fine, but the Worksheet name "Duplicates" varies and i need
to be able to select different cells - how can i make a pop up box to
type in the Month name, or just select the right worksheet, or even
column (although it is D:D on every worksheet),

If you can help - thanks in advance.