Adding some code to sort data into macro

The attached spread sheet has a macro which takes data from worksheet 'Stock' and presents the results in Worksheet 'Value'.  I would like four things done to this macro please.

1.  When Macro is run sort data 1-999 in the column with the heading 'Category'
2.  The cell B13 in the 'Value' worksheet keeps appearing in bold I would like it to not be in bold.
3.  The heading 'Value' in worksheet 'Value' to be bold please.
4.  Could the two headings 'Category & Value' both be postioned to the right of the cell they are in.

Any help greatly appreciated
Who is Participating?
Dave BrettConnect With a Mentor Vice President - Business EvaluationCommented:

Pls see code and attached workbook below


Option Explicit
Sub values()
    Dim ws1, ws2 As Worksheet
    Dim lastrow, destlast As Long
    Dim cell As Range, myrange As Range
    Set ws1 = Sheets("Stock")
    Set ws2 = Sheets("Value")
    lastrow = ws1.Range("F" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    ws1.Range("A:A").AdvancedFilter Action:=xlFilterCopy, _
                                    CopyToRange:=ws2.Range("A1"), Unique:=True
    destlast = ws2.Range("A" & Rows.Count).End(xlUp).Row
    For Each cell In ws2.Range("A2:A" & destlast)
        cell.Offset(0, 1).Formula = "=SUMPRODUCT((Stock!$A$2:$A$" & lastrow & "=A" & cell.Row & ")*((Stock!$F$2:$F$" & lastrow & _
                                    ")),(Stock!$A$2:$A$" & lastrow & "=A" & cell.Row & ")*(Stock!$G$2:$G$" & lastrow & "))"
    Next cell
    Set myrange = ws2.Range([a1], Cells(ws2.Rows.Count, "B").End(xlUp))
    ws2.Range("A1").Value = "Category"
    ws2.Range("B1").Value = "Value"
    myrange.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    ws2.Range("A1:B1").Font.Bold = True
    ws2.Range("A1:B1").HorizontalAlignment = xlRight
 End Sub

Open in new window

SimonreproAuthor Commented:
Very Good Thanks!
Dave BrettVice President - Business EvaluationCommented:
No probs, thx for the grade :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.