Sorting data

Sub M()
Dim a, b, c As Double
c = 0
b = Range("A1048576").End(xlUp).Row
For a = b To 2 Step -1
Range("D" & a).Select
If Range("D" & a).Value <> Range("D" & a - 1).Value Then
c = c + 1
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Copy
Workbooks.Add
Range("A1").Value = "Transaction Status"
Range("B1").Value = "Transaction ID"
Range("C1").Value = "Transaction Date"
Range("D1").Value = "RD Reference"
Range("E1").Value = "Amount"

Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False

ActiveWorkbook.SaveAs Filename:="C:\Users\sense soft\Documents\Book" & c, FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close
ActiveCell.Offset(-1, 0).Select
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
End If
Next a
End Sub

LauraDaisy
Copper Contributor
May 09, 2018
So I tried this (thankyou v.much :) Logaraj Sekar )

I tried it on a new set of data - it worked but pulled out 30 CSV files.

When I did it manually there are only 2 spreadsheets.

Any ideas? I dont know anything about VBA code so not sure where to start.

Thanks
:)
- Logaraj Sekar
  Steel Contributor
  May 09, 2018
  Hi Laura,
  
  Based on the RD reference, i created macro. So it will count no of RD references (without duplication).
  
  If RD reference count is 30, it will create 30 Sheets. In case you need change, explain it i will do changes in macro.
  - LauraDaisy
    Copper Contributor
    May 10, 2018
    Hi Logaraj Sekar
    
    I think there may have been some confusion in what I'm trying to do.
    
    I have a data set of 600 lines of transaction data with dates and references. In that, 100 are duplicate reference numbers but have different transaction dates. All the rest are unique reference numbers.
    
    I need to move the data so that one csv. file doesn't contain the same reference number.
    
    When I do it manually, I ended up with 3 csv. files - 1 ) of 500 lines, 2 ) of 63 lines, 3) of 37 lines.
    
    Then I can import in bulk.
    
    Does that make sense?
    
    if it can be done it would be awesome as manually is quite time consuming and requires several checks to ensure no mistake on my part.
    
    Thanks :)

Forum Discussion