Excel 2013 - Is there a macro to sort and split the contents of a spreadsheet

0

I know there any many similar questions to this, but none seem to match exactly... I have a basic Stock List, and want to help to speed up stocktaking, so am looking to create a macro to do the below:

Select a range and sort column E, F and C, then where Column E has a value change, insert a blank coloured line.

===========================================================================================

So im very new to VBA, but have managed to create a 3 part macro:

Macro1 - Sorts the data Macro2 - Inserts a blank line at value change Macro3 - Assigned to a button, this runs Macro1 and Macro2

All I need to do now, is change the blank line coloured???

Sub sort()
Rows("5:5").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.Worksheets("HEAT SEAL").sort.SortFields.Clear
ActiveWorkbook.Worksheets("HEAT SEAL").sort.SortFields.Add Key:=Range( _
    "E5:E407"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
ActiveWorkbook.Worksheets("HEAT SEAL").sort.SortFields.Add Key:=Range( _
    "A5:A407"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
ActiveWorkbook.Worksheets("HEAT SEAL").sort.SortFields.Add Key:=Range( _
    "F5:F407"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("HEAT SEAL").sort
    .SetRange Range("A5:J407")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub

Sub Split()
rw = 5
myval = ActiveSheet.Cells(rw, 5).Value
While myval <> ""
While myval = ActiveSheet.Cells(rw, 5).Value
rw = rw + 1
Wend
ActiveSheet.Rows(rw).EntireRow.Insert
rw = rw + 1
myval = ActiveSheet.Cells(rw, 5).Value
Wend
End Sub

Sub run()
    Application.run "STOCK1.xls!sort"
    Application.run "STOCK1.xls!Split"
End Sub

user3106405

Posted 2013-12-16T08:12:42.903

Reputation: 1

1I'm sure they do exist. Any way, what have you tried so far, or do you want some one to just do your work for you? It may be easier if you share the code you've tried so we can see where it has gone wrong. – Dave – 2013-12-16T08:29:53.657

Im still very new to VBA, but here are a few macros I have tried: – user3106405 – 2013-12-16T08:34:56.247

No answers