Create Pivot Table using Macro

[Hassesbar]

Hi,
The Macro below works for a specific data source....how do I change it to work for any worksheet that I'm in when I run the macro, i.e creates a pivot table with all data in that sheet. The headings will always be called the same name

Thanks

Sub SchedulePivot()
'
' SchedulePivot Macro
' Macro recorded 15/01/2004 by ChristoferBeckman
'

'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'sched export 15.1.04 (2)'!R1C1:R232C9").CreatePivotTable TableDestination:= _
"", TableName:="PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="pub_date", _
ColumnFields:="class_desc"
ActiveSheet.PivotTables("PivotTable1").PivotFields("M+S").Orientation = _
xlDataField
End Sub

 

[pcc]

try this (not tested)

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
[a1].CurrentRegion.Address).CreatePivotTable TableDestination:= _
"", TableName:="PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="pub_date", _
ColumnFields:="class_desc"
ActiveSheet.PivotTables("PivotTable1").PivotFields("M+S").Orientation = _
xlDataField

 

[Hassesbar]

Thanks a lot, works perfectly!!!