CODEWITHSUNDEEP
excelvba
Lucas Biggio
Lucas Biggio

Reputation: 1

How to copy a range of data based on the unique values in another column and paste it in another sheet

I have attached an image here of the data set for further clarification

I receive a large amount of data in a combined.csv which me requires to sort them out by a serial number and seperate them as their own .csv file. I have managed to extract that serial number, but I can't seem to figure out how to select the unique range of data and then paste that into another sheet. Here's what I have so far:

Sub CleanData()
Range("A:A").Sort Key1:=Range("A:A"), Order1:=xlDescending, Header:=xlYes
Range("W1").Value = "Receipt Number"
'Column W to same length as Column A
With Range(Range("W2"), Cells(Rows.Count, 1).End(xlUp).Offset(0, 22))
    .Formula = "=Left(A2, 4)" 'Apply Formula, with auto-fill-down
    .Calculate 'Calculate results of Formulae
    .Value = .Value 'Convert Formulae to Values
Range("W:W", Range("W:W").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=Range("AA2"), Unique:=True


End With
End Sub

Thank you in advance!

Upvotes: 0

Views: 63

Answers (1)

mohagali
mohagali

Reputation: 410

you can use this sample on your work

Sub CleanData()

    Sheet1.Range("B1:B3") = "=left(a1,4)"
    Sheet2.Range("A1:A3").Value = Sheet1.Range("B1:B3").Value

End Sub

before:

enter image description hereenter image description here

after:

enter image description hereenter image description here

Upvotes: 1

Related Questions