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

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!!

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):

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."

There's an Workbook.ActiveSheet property which you can use.