• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 274
  • Last Modified:

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
Monthly-Stock-Valuation.xls
0
Simonrepro
Asked:
Simonrepro
  • 2
1 Solution
 
Dave BrettCommented:
Hi,

Pls see code and attached workbook below

Cheers

Dave
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
    ws2.Cells.ClearContents
    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, _
        DataOption1:=xlSortNormal
    ws2.Range("A1:B1").Font.Bold = True
    ws2.Range("A1:B1").HorizontalAlignment = xlRight
 End Sub

Open in new window

Monthly-Stock-Valuation-ejb-.xls
0
 
SimonreproAuthor Commented:
Very Good Thanks!
0
 
Dave BrettCommented:
No probs, thx for the grade :)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now