I am a newbie to VBA. I ned to do the following:
-I need to create a pivot table from multiple worksheets.
-The pivot table should be in the same workbook
-I need to get data from only selcted sheets. So the code should allow me to specify the sheet names for the pivot table
-All these source worksheets have same columns and all have 1st row as column headers which are also same
-I want the pivot table to refresh automatically whenever the sheets are updated

Please help me how to do this.

Forgot to mention. I got this code form one of the threads and its working fine, but need help to have couple of updates to the code:
-Need to create the pivot in the same workbook as the source worksheets
-Need to have the data fields horizontally(like columns) instead of rows. I know how to drag it manually, but I want that to be in this code.
-Whenever there is update in data to one of the source worksheets, this pivot needs ot be refreshed automatically
-Need to add calculated fields. How do i add it into this code

Sub test()

Dim i As Long
Dim arSQL() As String
Dim objPivotCache As PivotCache
Dim objRS As Object
Dim wbkNew As Workbook
Dim wks As Worksheet

With ActiveWorkbook
ReDim arSQL(1 To .Worksheets.Count)
For Each wks In .Worksheets
i = i + 1
arSQL(i) = "SELECT * FROM [" & wks.Name & "$]"
Next wks
Set wks = Nothing
Set objRS = CreateObject("ADODB.Recordset")

objRS.Open Join$(arSQL, " UNION ALL "), Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _
.FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)
End With

Set wbkNew = Workbooks.Add(Template:=xlWBATWorksheet)

With wbkNew
Set objPivotCache = .PivotCaches.Add(xlExternal)
Set objPivotCache.Recordset = objRS
Set objRS = Nothing

With .Worksheets(1)
objPivotCache.CreatePivotTable TableDestination:=.Range("A3")
Set objPivotCache = Nothing

With .PivotTables(1)
.PivotFields("Associates _FTE & OSC'S").Orientation = xlRowField
.PivotFields("Group").Orientation = xlPageField
.PivotFields("Task").Orientation = xlPageField
.PivotFields("1Q10").Orientation = xlDataField
.PivotFields("2Q10").Orientation = xlDataField
.PivotFields("3Q10").Orientation = xlDataField
End With
End With
End With
Set wbkNew = Nothing
End Sub