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
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