Pivot Table W/ multiple sources VBA Problem

[Bigzippy]

I am trying to use named ranges from multiple worksheets to create a pivot table but it will not work. Any ideas?

CODE:
Sheets("sheet1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlConsolidation, SourceData:= _
Array("name", "name", "name", "name", "name", "name", "name", _
"name", "name")).CreatePivotTable TableDestination:=Range("A1"), _
TableName:="PivotTable6"
With ActiveSheet.PivotTables("PivotTable6")
.ColumnGrand = False
.HasAutoFormat = False
.RowGrand = False
.SmallGrid = False
End With
ActiveSheet.PivotTables("PivotTable6").PivotFields("Data").PivotItems( _
"Sum of Value").Position = 1
End Sub

 

[Bigzippy]

Anyone know if this can be done?
Makes my book more efficient not having to copy all these ranges to sheet1

 

[Bigzippy]

Solution

Sub test()
Application.ScreenUpdating = False
Dim wsn As String
Dim i As Integer
Dim prange As String
Dim PCrange As String
Dim rangearr() As String

ReDim Preserve rangearr(i To Worksheets.Count - 5)

For i = 2 To Worksheets.Count - 3 Step 1
wsn = Worksheets(i).Name
Sheets(wsn).Select
Range("B9:D9").Select
Range(Selection, Selection.End(xlDown)).Select
prange = Selection.Address(ReferenceStyle:=xlR1C1, _
RowAbsolute:=True, _
ColumnAbsolute:=True)
PCrange = wsn & "!" & prange
If (i - 3) < 0 Then rangearr(0) = PCrange _
Else: rangearr(LBound(rangearr) + (i - 2)) = PCrange
Next i

Sheets("sheet1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlConsolidation, SourceData:= _
rangearr).CreatePivotTable TableDestination:=Range("a1"), _
TableName:="PivotTable1"
With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = False
.HasAutoFormat = False
.RowGrand = False
.SmallGrid = False
End With
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").PivotFields("Data").PivotItems( _
"Sum of Value").Position = 1

Range("D1").Select
Application.CommandBars("PivotTable").Visible = False

End Sub