Macro Help

[NPell]

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 on every worksheet),

If you can help - thanks in advance.

 

[Bob Phillips]

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]

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

    Set Target = Application.InputBox("Select any cell on the targetsheet
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)

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

 

[Bob Phillips]

Leave which Y where?

--
---
HTH

Bob


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

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)

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

 

[NPell]

Leave which Y where?

--
---
HTH

Bob

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

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

Bob

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

news:343b534b-7f62-49f4-b436-5e43b7b2e92d@m45g2000hsb.googlegroups.com...

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 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?

 

[Bob Phillips]

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)

Leave which Y where?

--
---
HTH

Bob

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

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

Bob

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

news:343b534b-7f62-49f4-b436-5e43b7b2e92d@m45g2000hsb.googlegroups.com...

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 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?

 

[NPell]

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)

Leave which Y where?

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

"NPell" <[email protected]> wrote in message

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)

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