D
Dave K
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:=xlDatabase,
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!
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:=xlDatabase,
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!