VBA Consolidating Multiple Sheets in a Pivot Table

[c_clark]

Hi

I am new to the world of VBA and I found some code from another post that seemed to work perfectly for the post creator after community help. The code loops through all worksheets in the activeworkbook and consolidates these worksheets into one pivot table and creates the pivot in the active workbook on a new sheet. I am trying to get that to work for me but I keep running into a 'Run-time error 9' Subscript out of range for

arSQL(i) = "SELECT * FROM [" & wks.Name & "$]"


I have 9 worksheets in this workbook (sheets 1 & 2 already contain data that gets dynamically updated from other worksheets as they are updated and I have another macro already written to produce a pivot table report for me on sheet 3.) I need to get a this code to produce one pivot table report based on the data in sheets 4-8 (labeled "HBB33 Report Period (x)" respectively and output the data on to sheet 9 labeled "AGG DATA REPORT."


Can anybody help me to modify the code? Thank you for any help you can provide. I am trying to learn by doing .

Here is the full code:

Sub Create_PivotTable()
Dim i As Long
Dim arSQL() As String
Dim objPivotCache As PivotCache
Dim objRS As Object
Dim wks As Worksheet
Dim ws2 As Worksheet
With ActiveWorkbook
ReDim arSQL(4 To 12)
For Each wks In .Worksheets
Select Case wks.Name
Case "HBB33 REPORT PERIOD 1", "HBB33 REPORT PERIOD 2", "HBB33 REPORT PERIOD 3", "HBB33 REPORT PERIOD 4", "HBB33 REPORT PERIOD 5"
i = i + 1
arSQL(i) = "SELECT * FROM [" & wks.Name & "$]"
End Select
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)
Set objPivotCache = .PivotCaches.Add(xlExternal)
Set objPivotCache.Recordset = objRS
Set objRS = Nothing
End With
Set ws2 = Worksheets("AGG DATA REPORT")
With ws2
objPivotCache.CreatePivotTable TableDestination:=.Range("A3")
Set objPivotCache = Nothing
.Range("A3").Select
End With
End Sub

 

[c_clark]

Sorry if I was unclear. I am trying to get this code to produce a pivot table based on the data in from sheets 4-8. This code is the code related to those sheets only.