VBA Consolidating Multiple Sheets in a Pivot Table

Hi

I got a brilliant bit of code (which works perfectly) from Bill Jelens "Excel Gurus gone Wild" which loops through all worksheets in the activeworkbook and consolidates these worksheets into one pivot table, this example creates the pivot in a new workbook, what I would really like to do is add a new sheet and create the pivot in the active workbook where I am pulling the data from can anybody help me to modify the code?

I have not added the code yet to generate the pivot

Any help would be greatly appreciated

Kind Regards

Thomas

Code below:

Option Explicit
Sub bob()

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
Do Until wks.Name = ""
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
Range("A3").Select
End With
End With
Set wbkNew = Nothing
End Sub