Macro Help

  • Thread starter Thread starter NPell
  • Start date Start date
N

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

If you can help - thanks in advance.
 
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)
 
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..
Click to expand...
Sub FindDuplicates()
Click to expand...
   Sheets("Current Month").Select
   Range("R2").Select
Click to expand...
   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
Click to expand...
   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
Click to expand...
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),
Click to expand...
If you can help - thanks in advance.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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.
 
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..
Click to expand...
Sub FindDuplicates()
Click to expand...
Sheets("Current Month").Select
Range("R2").Select
Click to expand...
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
Click to expand...
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
Click to expand...
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),
Click to expand...
If you can help - thanks in advance.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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.
 
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
Click to expand...
Set Target = Application.InputBox("Select any cell on the target sheet
with the mouse", Type:=8)
If Not Target Is Nothing Then
Click to expand...
SheetName = Target.Parent.Name
With Sheets("Current Month")
Click to expand...
.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
Click to expand...

(there's no email, no snail mail, but somewhere should be gmail in my
addy)
Click to expand...
news:343b534b-7f62-49f4-b436-5e43b7b2e92d@m45g2000hsb.googlegroups.com...
Click to expand...
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 -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

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 -
Click to expand...

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?
 
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
Click to expand...
Set Target = Application.InputBox("Select any cell on the target sheet
with the mouse", Type:=8)
If Not Target Is Nothing Then
Click to expand...
SheetName = Target.Parent.Name
With Sheets("Current Month")
Click to expand...
.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
Click to expand...

(there's no email, no snail mail, but somewhere should be gmail in my
addy)
Click to expand...
news:343b534b-7f62-49f4-b436-5e43b7b2e92d@m45g2000hsb.googlegroups.com...
Click to expand...
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 -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

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 -
Click to expand...

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?
 
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)
Click to expand...
"NPell" <[email protected]> wrote in message
Click to expand...
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:D on every worksheet),
If you can help - thanks in advance.- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
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
Click to expand...
But thats minor, thanks very much Bob.
Click to expand...
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 -
Click to expand...
- Show quoted text -
Click to expand...

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 -
Click to expand...

Awesome, thanks mate.
 
Back
Top