Excel macro to select a dynamic range to generate a pivot table

I want to automate generating an expense report using a pivot table.

I want to use expense data that is reported in a variable (dynamic) range that always starts in the same cell, but it ends depending on how many expense entries there are - always starts in A10, but the ending cell is variable (i.e. could be J64 one time or K89 another time.)

When I record a macro to select the range by starting in cell A10, holding shift+ctl+end, it always creates a static range instead of "finding" the dynamic end cell.  - like 'ExpenseDetail'!R10C1:R48C9"

Below is some code that I currently have.  How should this code be to find the changing address of the last cell in the data range and generates the pivot table using all of the expense data in that report?

Attached are 2 sample expense files with data that I want to run the macro on.  Each has a different number of expenses in it.
Sub a_format_iXpense_report()
'
' a_format_iXpense_report Macro
' Macro recorded 3/27/2010 
'

'
    ActiveSheet.Select
    ActiveSheet.Name = "ExpenseDetail"
    Range("A10").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Range("H11").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Style = "Comma"
    Range("B8").Select
    ActiveCell.FormulaR1C1 = "Me"
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A10").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'ExpenseDetail'!R10C1:R48C9").CreatePivotTable TableDestination:="", _
        TableName:="ExpenseSummary", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("ExpenseSummary").AddFields RowFields:="Date", _
        ColumnFields:="Category"
    ActiveSheet.PivotTables("ExpenseSummary").PivotFields("Amount").Orientation = _
        xlDataField
    ActiveCell.SpecialCells(xlLastCell).Select
    Range(Selection, Selection.End(xlUp)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
'    ActiveCell.Offset(-12, -8).Range("A1:I13").Select
    ActiveCell.Activate
    Selection.Style = "Comma"
    Selection.End(xlUp).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

Open in new window

example1.csv
example2.csv
mycomacAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Try changing:

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'ExpenseDetail'!R10C1:R48C9").CreatePivotTable TableDestination:="", _
        TableName:="ExpenseSummary", DefaultVersion:=xlPivotTableVersion10

to:

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'ExpenseDetail'!$A$10:" & Range("a10").SpecialCells(xlLastCell).Address).CreatePivotTable TableDestination:="", _
        TableName:="ExpenseSummary", DefaultVersion:=xlPivotTableVersion10
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mycomacAuthor Commented:
Thanks - that works.  Just a quick followup if you can.  If I want to select a range that has a fixed starting point ($A$10), and a variable end point defined at ctrl+end, how would you do that?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.