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?
Printable View
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?
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!
Kindly see attached sheet I want duplicated numbers to be copied from series sheet to duplicated numbers sheet.
[vba]
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
[/vba]
A better method to filter results using a helper column
[VBA]
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
[/VBA]
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!
Thank you very much Sir it works great.
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!
i guess it was me Malcolm!Quote:
Originally Posted by Decent_Boy at Ozgrid
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.