So I've got a start on the code to sort a list of materials where I can enter the materials how I want (seen here) and then it will sort to the correct way as seen in the second picture.
Where I've got the "Let the Magic Happen v2" button to automatically sort the list to the way I want it, but I want to then add a new line (that is also still formatted with the boxes and formulas) like this:
This is my current code, but I'm stuck on how to add a "blank" line in between the different materials/sizes:
I appreciate any help that you can provide, thanks!
Where I've got the "Let the Magic Happen v2" button to automatically sort the list to the way I want it, but I want to then add a new line (that is also still formatted with the boxes and formulas) like this:
This is my current code, but I'm stuck on how to add a "blank" line in between the different materials/sizes:
VBA Code:
Private Sub Let_The_Magic_Happen_v2_Click()
Application.ScreenUpdating = False
' Sorts the worksheet
ActiveWorkbook.Worksheets("Quote_and_Cut").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Quote_and_Cut").Sort.SortFields.Add2 Key:=Range("J35:J" & Cells(Rows.Count, "J").END(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"HRT,LASER,DELRIN,HDPE,8#XLPE", DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Quote_and_Cut").Sort.SortFields.Add2 Key:=Range("L35:L" & Cells(Rows.Count, "J").END(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= xlSortNormal
ActiveWorkbook.Worksheets("Quote_and_Cut").Sort.SortFields.Add2 Key:=Range("M35:M" & Cells(Rows.Count, "J").END(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= xlSortNormal
ActiveWorkbook.Worksheets("Quote_and_Cut").Sort.SortFields.Add2 Key:=Range("N35:N" & Cells(Rows.Count, "J").END(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= xlSortNormal
ActiveWorkbook.Worksheets("Quote_and_Cut").Sort.SortFields.Add2 Key:=Range("O35:O" & Cells(Rows.Count, "J").END(xlUp).Row), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= xlSortNormal
With ActiveWorkbook.Worksheets("Quote_and_Cut").Sort
.SetRange Range("A35:W" & Cells(Rows.Count, "J").END(xlUp).Row)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
' Fixes the Cut List Subtotal SUM, because Excel tries to change it during sort (this part isn't really too relevant to this post, but wanted to include it just in case)
Dim ThisPos As Range
With Range("U1:U32000")
Set ThisPos = .Find(What:="CUT LIST SUBTOTAL", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not ThisPos Is Nothing Then
Cell_Add= Split(ThisPos.Address, "$")
ThisRow = Cell_Add(1)
ThisCol = Cell_Add(2)
ActiveWorkbook.Worksheets("Quote_and_Cut").Range("W" & ThisCol).Value = "=SUM(W35:W" & (ThisCol - 1) & ")"
Else
msgboxresponse = MsgBox("CUT LIST SUBTOTAL not found")
End If
End With
Application.ScreenUpdating = True
End With
End Sub
I appreciate any help that you can provide, thanks!