Sorting data by duplicates

[Mike2502]

Hi All,

I have a piece of code which I have put below, I need to try and somehow alter it slightly.

The code below sorts the data by seeing whether a cell color in Column A is 255, 0, 0 and then it will copy and paste it onto sheet "ToDo" and then delete it on the original spreadsheet... however I want to change it where if there is;

2 Duplicates = Gets copied onto spreadsheet "ToDo_2"​

3 Duplicates = Gets copied onto spreadsheet "ToDo_3"​

4 Duplicates = Gets copied onto spreadsheet "ToDo_4"​

​

I hope this makes sense and thanks in advance

Sub Sort_Data()
  
            With Sheets("Sort")

         If .AutoFilterMode Then .AutoFilterMode = False
              With .Range("A1:O50" & .Range("A" & Rows.Count).End(xlUp).Row)
                  .AutoFilter 1, RGB(255, 0, 0), xlFilterCellColor
                  .Offset(1).Copy Sheets("ToDo").Range("A" & Rows.Count).End(xlUp)(2)
                  .Offset(1).Delete
                  Sheets("Sort").ShowAllData
          End With
         
   Range("A2").Select   
End Sub

 

[Peter_SSs]

Only issue is when it sorts the data and I'm back to the original sheet "Sort" all the data is hidden? Any way to unhide this?

Based your own code from post #1 I assumed that 'Sort' was the active sheet when you run the code. If that is the case then there should be no leaving of that sheet caused by my code & therefore no coming back to it. In any case, I am not experiencing any hidden rows issue at the end, due to ..

.ShowAllData

.. before the code ends.

Have you modified the code in any way?

If 'Sort' is not, or may not be, the active sheet when the code is run, then the code will need slight modification anyway if it is to 'Select' cell A2 on 'Sort' at the end.

 

[Mike2502]

Based your own code from post #1 I assumed that 'Sort' was the active sheet when you run the code. If that is the case then there should be no leaving of that sheet caused by my code & therefore no coming back to it. In any case, I am not experiencing any hidden rows issue at the end, due to ..

.ShowAllData

.. before the code ends.

Have you modified the code in any way?

If 'Sort' is not, or may not be, the active sheet when the code is run, then the code will need slight modification anyway if it is to 'Select' cell A2 on 'Sort' at the end.

Hi Peter, just tried the code absolute amazing mate!

Just an additional request if you could if not no worries! - Is there any chance we could make it only export 1 duplicate instead of all?

So.. for instance lets say theres 3 rows of 'ABC123' instead of copying and pasting all 3 ABC123 rows only the first row and then continue to delete the other two ?

Bit of a long shot to be honest but grateful if you could

 

[Peter_SSs]

say theres 3 rows of 'ABC123' instead of copying and pasting all 3 ABC123 rows only the first row and then continue to delete the other two ?

Only delete two? Are you saying that you now want 1 of the 3 rows left behind in the 'Sort' sheet? Previously we deleting all the duplicates.

 

[Mike2502]

Only delete two? Are you saying that you now want 1 of the 3 rows left behind in the 'Sort' sheet? Previously we deleting all the duplicates.

Just trying something out mate so e.g

2 duplicates on sheet Sort, copy and paste 1 of the rows over to sheet ToDo_1 and delete both from sheet sort

E.g. Again

3 duplicates on sheet Sort, copy and paste 1 of the rows over to sheet ToDo_1 and delete them all from sheet sort

So in essence just copy and paste 1 instead of the all rows

 

[Peter_SSs]

So in essence just copy and paste 1 instead of the all rows

Just add this red code to the relevant line

.Offset(1).SpecialCells(xlVisible).Rows(1).Copy Sheets("ToDo_" & d(itm)).Range("A" & Rows.Count).End(xlUp)(2)