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?
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!
Regards,
Simon
Please read this before cross posting!
In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
If I have seen further it is by standing on the shoulders of giants.
Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)
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]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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!
Regards,
Simon
Please read this before cross posting!
In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
If I have seen further it is by standing on the shoulders of giants.
Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)
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!
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
i guess it was me Malcolm!Originally Posted by Decent_Boy at Ozgrid
Regards,
Simon
Please read this before cross posting!
In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
If I have seen further it is by standing on the shoulders of giants.
Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)
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.
Posting Permissions
Reply With Quote
