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
Bookmarks