Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2010
    Posts
    3

    Unanswered: Creating a macro to sort and then subtotal a preselected range of data

    Hello,

    I'm working with financial transactions, so the excel sheets I download from a system can have varying numbers of rows. My ultimate goal is to sort and then subtotal the data. Because the number of rows I want to sort and subtotal are always different, I was thinking I could make a macro that would only work off of preselected data. I can't just work off of all active cells, as there are some rows I don't want to include in the sort and subtotal (i.e. transactions past the billing date are all at the bottom and I insert 8 blank rows between transactions that will be included and those that won't...so I'm only sorting and subtotaling the rows above the 8 empty rows).

    I'm new to making macros, so I've recorded the following simple macro to sort and subtotal data, however, you'll note it only works for the cells that were selected when I recorded the macro. I have a little knowledge of going in and actually manually modifying the code, so any help would be appreciated.

    -----------------------------------------------------

    Sub SubtotalTransDetailQuery()
    '
    ' SubtotalTransDetailQuery Macro
    '
    ' Keyboard Shortcut: Ctrl+w
    '
    ActiveWorkbook.Worksheets("Billable").Sort.SortFie lds.Clear
    ActiveWorkbook.Worksheets("Billable").Sort.SortFie lds.Add Key:=Range("E2:E21" _
    ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("Billable").Sort.SortFie lds.Add Key:=Range("F2:F21" _
    ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Billable").Sort
    .SetRange Range("A1:Y21")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Selection.Subtotal GroupBy:=5, Function:=xlSum, TotalList:=Array(8), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    End Sub

  2. #2
    Join Date
    Dec 2010
    Posts
    3
    This is about as fancy as I've been able to get....after creating this recorded macro, I know enough to just go in and change it to "ActiveSheet" instead of the specific sheet I recorded on, so the macro works in any active sheet. I thought that was pretty cool...imagine how amazed I'll be if you can help me solve my sort and subtotal issue!!

  3. #3
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Welcome to the forum.

    If your data always starts in A1 and the area you want to sort etc is contiguous then one easy option would be to use the Range.CurrentRegion property. Something like this (untested):

    Code:
    Sub SubtotalTransDetailQuery()
    '
    ' SubtotalTransDetailQuery Macro
    '
    ' Keyboard Shortcut: Ctrl+w
    '
        Dim wstBillable As Worksheet
        Dim rngData As Range
     
        Set wstBillable = ActiveWorkbook.Worksheets("Billable")
        Set rngData = wstBillable.Range("A1").CurrentRegion
     
        With wstBillable.Sort
            With .SortFields
                .Clear
                .Add Key:=Application.Intersect(rngData, wstBillable.Range("E:E")), _
                        SortOn:=xlSortOnValues, _
                        Order:=xlAscending, _
                        DataOption:=xlSortTextAsNumbers
     
                .Add Key:=Application.Intersect(rngData, wstBillable.Range("F:F")), _
                        SortOn:=xlSortOnValues, _
                        Order:=xlAscending, _
                        DataOption:=xlSortNormal
            End With
            .SetRange rngData
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
     
        rngData.Subtotal GroupBy:=5, _
                            Function:=xlSum, _
                            TotalList:=Array(8), _
                            Replace:=True, _
                            PageBreaks:=False, _
                            SummaryBelowData:=True
     
        wstBillable.Outline.ShowLevels RowLevels:=2
     
    End Sub

  4. #4
    Join Date
    Dec 2010
    Posts
    3
    Excuse my lack of knowledge, I'm sure it is simple, but how would this all be changed to make it work on any active sheet I run it on? The worksheet isn't always called "billable."

  5. #5
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    There's an Workbook.ActiveSheet property which you can use.
    Code:
        Dim wstTarget As Worksheet
        Dim rngData As Range
        
        If ActiveWorkbook.ActiveSheet Is Worksheet Then
            Set wstTarget = ActiveWorkbook.ActiveSheet
       
            'etc....

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •