Excel Macro - Create Pivot Table & Chart.

Here the data which will be used for creating the pivot table and cahrt is stored in a worksheet calles "Action1".

Sub CreatePivotTable()
' CreatePivotTable Macro'
' Keyboard Shortcut: Ctrl+Shift+F

FinalColumn = 9
' The final column which contains data in the excelsheet

Row = Sheets("Action1").UsedRange.Rows.Count
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Action1!R1C1:R" & Row & "C"&FinalColumn).CreatePivotTable TableDestination:=""_
,TableName:= "PivotTable1", DefaultVersion:=xlPivotTableVersion10

ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

With ActiveSheet.PivotTables("PivotTable1").PivotFields("PROJECT_ID")
.Orientation = xlColumnField
.Position = 1
End With

With ActiveSheet.PivotTables("PivotTable1").PivotFields("PS_NAME")
.Orientation = xlRowField
.Position = 1
End With

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _ "PivotTable1").PivotFields("HOURS"), "Sum of HOURS", xlSum

For Each wksht In ActiveWorkbook.Worksheets
If StrComp(wksht.Name, "Action1", 1) <> 0 Then
PivotSheet = wksht.Name
End If
Next wksht

' Create Pivot Chart
ActiveSheet.UsedRange.Select
Charts.Add
ActiveChart.SetSourceData Source:=Sheets(PivotSheet).UsedRange
ActiveChart.Location Where:=xlLocationAsNewSheet

End Sub