Cut and Paste Rows to another sheet based on duplicate value
Good Afternoon
I am looking for a macro which will compare the values in column B and if duplicates exist, cut each instance of that duplicate row and paste it in a new sheet. For example:
A B
Test 1
Test 1
Test 2
Test 3
Test 3
In this example the rows with 1 and 3 would be cut and pasted into a new sheet.
Thanks
Microsoft ApplicationsMicrosoft OfficeMicrosoft Excel
Last Comment
gowflow
8/22/2022 - Mon
etech0
You can try something like this:
Sub Duplicates()Dim x As IntegerColumns("A:B").SelectRange("B1").ActivateActiveSheet.Sort.SortFields.ClearActiveSheet.Sort.SortFields.Add Key:=Range("B1"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormalWith ActiveSheet.Sort .SetRange Range("A:B") .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .ApplyEnd Withx = 2Do While Cells(x, 1).Value <> "" If Cells(x, 2).Value = Cells(x - 1, 2).Value Then Rows(x).Select Selection.Cut Sheets("NameOfSecondSheet").Select Range("A1").Select Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Select Loop ActiveSheet.Paste Application.CutCopyMode = False Sheets("NameOfFirstSheet").Select End If x = x + 1LoopEnd Sub
Sorry for the delay in my reply, its been a busy weekend :(
Thank you for your reply's, I am sorry but I do not think I was clear enough, I require that all the rows with duplicates are copied, including the original row. i.e.
Test 1
Test 1
Both rows would be copied to the new sheet, not just one row.
Open in new window