VBA Consolidating Multiple Sheets In A Pivot Table

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

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 = ""..............