Macro Help
 

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.

Macro Help
 

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.

Macro Help
 

On Apr 28, 9:32*am, "Bob Phillips" wrote:
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.- Hide quoted text -

- Show quoted text -

This is brilliant, thankyou. I had to edit the end bit though, cos i
kept getting that the desination cells were different - so i changed
.Columns("R:R").Copy
To
Columns("R:R").Select
Selection.Copy

But thats minor, thanks very much Bob.

Ive tried it now with 2 worksheets, is there a way of getting to to
leave the Y if it is already there - rather than overwriting it??
Thanks again if someone can help with this tweak.

Macro Help
 

Leave which Y where?

--
---
HTH

Bob


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



"NPell" wrote in message
...
On Apr 28, 9:32 am, "Bob Phillips" wrote:
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.- Hide quoted text -

- Show quoted text -

This is brilliant, thankyou. I had to edit the end bit though, cos i
kept getting that the desination cells were different - so i changed
..Columns("R:R").Copy
To
Columns("R:R").Select
Selection.Copy

But thats minor, thanks very much Bob.

Ive tried it now with 2 worksheets, is there a way of getting to to
leave the Y if it is already there - rather than overwriting it??
Thanks again if someone can help with this tweak.

Macro Help
 

On Apr 28, 10:43*am, "Bob Phillips" wrote:
Leave which Y where?

--
---
HTH

Bob

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

"NPell" wrote in message

...
On Apr 28, 9:32 am, "Bob Phillips" wrote:





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.- Hide quoted text -

- Show quoted text -

This is brilliant, thankyou. I had to edit the end bit though, cos i
kept getting that the desination cells were different - so i changed
.Columns("R:R").Copy
To
* * Columns("R:R").Select
* * Selection.Copy

But thats minor, thanks very much Bob.

Ive tried it now with 2 worksheets, is there a way of getting to to
leave the Y if it is already there - rather than overwriting it??
Thanks again if someone can help with this tweak.- Hide quoted text -

- Show quoted text -

It shows if its a duplicate by putting a Y... but if i re-do it again
referencing it to another tab, it overwrites any Y thats there with a
space/blank cell if it isnt applicable again - can i get it to leave
it as a Y if its there?

Macro Help
 

Does this do what you want?

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")

If .Range("R2").Value < "Y" Then

.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 If
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
...
On Apr 28, 10:43 am, "Bob Phillips" wrote:
Leave which Y where?

--
---
HTH

Bob

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

"NPell" wrote in message

...
On Apr 28, 9:32 am, "Bob Phillips" wrote:





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.- Hide quoted text -

- Show quoted text -

This is brilliant, thankyou. I had to edit the end bit though, cos i
kept getting that the desination cells were different - so i changed
.Columns("R:R").Copy
To
Columns("R:R").Select
Selection.Copy

But thats minor, thanks very much Bob.

Ive tried it now with 2 worksheets, is there a way of getting to to
leave the Y if it is already there - rather than overwriting it??
Thanks again if someone can help with this tweak.- Hide quoted text -

- Show quoted text -

It shows if its a duplicate by putting a Y... but if i re-do it again
referencing it to another tab, it overwrites any Y thats there with a
space/blank cell if it isnt applicable again - can i get it to leave
it as a Y if its there?

Macro Help
 

On Apr 29, 10:18*am, "Bob Phillips" wrote:
Does this do what you want?

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")

* * * * * * If .Range("R2").Value < "Y" Then

* * * * * * * * .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 If
* * * * 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

...
On Apr 28, 10:43 am, "Bob Phillips" wrote:





Leave which Y where?

--
---
HTH

Bob

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

"NPell" wrote in message

...
On Apr 28, 9:32 am, "Bob Phillips" wrote:

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.- Hide quoted text -

- Show quoted text -

This is brilliant, thankyou. I had to edit the end bit though, cos i
kept getting that the desination cells were different - so i changed
.Columns("R:R").Copy
To
Columns("R:R").Select
Selection.Copy

But thats minor, thanks very much Bob.

Ive tried it now with 2 worksheets, is there a way of getting to to
leave the Y if it is already there - rather than overwriting it??
Thanks again if someone can help with this tweak.- Hide quoted text -

- Show quoted text -

It shows if its a duplicate by putting a Y... but if i re-do it again
referencing it to another tab, it overwrites any Y thats there with a
space/blank cell if it isnt applicable again - can i get it to leave
it as a Y if its there?- Hide quoted text -

- Show quoted text -

Awesome, thanks mate.