Pivot Table Macro - How to describe the Active range (so that themacro can be used on new sets of data)
 

Hello - I am new to macros, and I have come up with the following
code, in trying to build a macro that simply creates a pivot table
based on the active range. But I am having a problem in that I don't
know how to request the pivot to run for the active region in general
(so that I can reuse the macro on multiple tabs and multiple
worksheets), rather than the specific sheet that I originally create
the macro in.

Sub TestPasteAdmits8()

Range("A13:A14").Select
Selection.EntireRow.Delete
Range("A12").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _

' I BELIEVE THAT WHAT FOLLOWS IS MY PROBLEM (I NEED THIS TO REFER TO
THE ACTIVE SELECTION AND NOT THE RANGE FOR THAT PARTICULAR SHEET)

"Texas!R12C1:R137C21").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable9", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable9").AddFields RowFields:= _
"Treatment Setting Code", ColumnFields:="Eff Start Dt"
With ActiveSheet.PivotTables("PivotTable9").PivotFields ("Ext Rec
Num")
.Orientation = xlDataField
.Caption = "Count of Ext Rec Num"
.Function = xlCount
End With
Cells.Select
Selection.Copy
Sheets.Add
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1").Select
End Sub

Does anyone happen to know what code I should substitute in the above
macro so that I can reuse the macro on each report, regardless of the
title of the tab?

Thanks for any thoughts you may be able to provide!

Pivot Table Macro - How to describe the Active range (so that the
 

Try this instead. Create a Range object, get the range of the source data,
and then use that range object to create the PivotTable:

Dim PTsource As Range
Set PTsource = Range(Range("A12"), _
Range("A12").End(xlToRight).End(xlDown))

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
PTsource.Address).CreatePivotTable TableDestination:="", _
TableName:="PivotTable9", DefaultVersion:=xlPivotTableVersion10

This code is also more efficient than using multiple selects.

HTH,
Matthew Pfluger

"Dave K" wrote:

Hello - I am new to macros, and I have come up with the following
code, in trying to build a macro that simply creates a pivot table
based on the active range. But I am having a problem in that I don't
know how to request the pivot to run for the active region in general
(so that I can reuse the macro on multiple tabs and multiple
worksheets), rather than the specific sheet that I originally create
the macro in.

Sub TestPasteAdmits8()

Range("A13:A14").Select
Selection.EntireRow.Delete
Range("A12").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _

' I BELIEVE THAT WHAT FOLLOWS IS MY PROBLEM (I NEED THIS TO REFER TO
THE ACTIVE SELECTION AND NOT THE RANGE FOR THAT PARTICULAR SHEET)

"Texas!R12C1:R137C21").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable9", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable9").AddFields RowFields:= _
"Treatment Setting Code", ColumnFields:="Eff Start Dt"
With ActiveSheet.PivotTables("PivotTable9").PivotFields ("Ext Rec
Num")
.Orientation = xlDataField
.Caption = "Count of Ext Rec Num"
.Function = xlCount
End With
Cells.Select
Selection.Copy
Sheets.Add
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1").Select
End Sub

Does anyone happen to know what code I should substitute in the above
macro so that I can reuse the macro on each report, regardless of the
title of the tab?

Thanks for any thoughts you may be able to provide!