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