PDA

View Full Version : Copy Duplication



ayazgreat
03-29-2008, 11:55 PM
Hi

I have two sheets one is Series and other is duplication I need a macro to search duplicated numbers in Series sheet in column c and copy them in duplication sheet. Is that possible?

Simon Lloyd
03-30-2008, 12:40 AM
ayazgreat, you will need to give a better description of what you require or post a sample workbook in order for you to recieve the help you need!

ayazgreat
03-30-2008, 01:54 AM
Kindly see attached sheet I want duplicated numbers to be copied from series sheet to duplicated numbers sheet.

mdmackillop
03-30-2008, 02:30 AM
Option Explicit
Sub GetDups()
Dim Rng As Range, cel As Range
Dim i As Long
i = 1
With Sheets("Series")
.Cells(1, 1).Resize(, 4).Copy Sheets("Duplication").Cells(1, 1)
Set Rng = Range(.Cells(1, 2), .Cells(Rows.Count, 2).End(xlUp))
For Each cel In Rng
If Application.CountIf(Range(.Cells(1, 2), cel), cel) = 2 Then
i = i + 1
.Cells(cel.Row, 1).Resize(, 4).Copy Sheets("Duplication").Cells(i, 1)
End If
Next
End With
End Sub

mdmackillop
03-30-2008, 02:49 AM
A better method to filter results using a helper column

Sub GetDups2()
Dim Rng As Range, cel As Range
Dim i As Long, col As Long

col = 10 'Helper column; adjust to suit

i = 1
With Sheets("Series")
Set Rng = Range(.Cells(1, 2), .Cells(Rows.Count, 2).End(xlUp))
Rng.Offset(, col - 2).FormulaR1C1 = "=COUNTIF(R1C2:RC2,RC2)"
.Columns(col).AutoFilter Field:=1, Criteria1:="2"
Rng.Offset(, -1).Resize(, 4).SpecialCells(xlCellTypeVisible).Copy _
Sheets("Duplication").Range("A1")
.Columns(col).AutoFilter
.Columns(col).ClearContents
End With
End Sub

Simon Lloyd
03-30-2008, 02:50 AM
It seems that i answered the post here!
http://www.ozgrid.com/forum/showthread.php?t=87331
ayazgreat or decent boy you must supply a link to all your cross posts or risk them being deleted!

ayazgreat
03-30-2008, 02:52 AM
Thank you very much Sir it works great.

mdmackillop
03-30-2008, 02:56 AM
Would you please advise which of us (Simon or I) wasted our time by your posting the same question in two forums concurrently? This can get you suspended or banned.

Isn't the qustion title ironical!

Simon Lloyd
03-30-2008, 03:10 AM
Re: Copy Duplicated Numbers Between 2 Sheets
Thanks
i guess it was me Malcolm!

ayazgreat
03-30-2008, 03:23 AM
Sir

Sorry for misunderstanding I and my friend are working togather we both tried to find out solution our question but he has got different views than me for this solution. but I accept that we both made mistake we are really sorry for that but next time we would be very careful in this regard.