Loading pivotfields from an array
 

I'm using th code below, is this the most efficient way to use the array?

Sub Load_PivotFields()
Dim DataFieldArray As Variant, Pivot_Field As String, Sht_Select As String
Application.ScreenUpdating = False
Sheets("Formulas").Select
DataFieldArray = Range(Cells(100, 1), Cells(147, 8)).Value
For w = 1 To 8
ActiveSheet.Next.Select
Sht_Select = ActiveSheet.Name
For x = 1 To UBound(DataFieldArray)
Pivot_Field = DataFieldArray(x, w)
ActiveSheet.PivotTables("PivotTable1").AddDataFiel d
ActiveSheet.PivotTables("PivotTable1").PivotFields (Pivot_Field), "Sum of " &
Pivot_Field, xlSum
Nex x
Next w
Application.ScreenUpdating = True
End Sub

--
Helping Is always a good thing

Loading pivotfields from an array
 

hi,

Your use of the array looks fine to me. Overall, my below version
should be slightly more efficient because I have removed the use of
".Select" & have grouped object references using With clauses.


Code:
--------------------
Option Explicit
Sub Load_PivotFields()
Dim DataFieldArray As Variant, Pivot_Field As String, Sht_Select As String
Dim StartgSht As Long
Application.ScreenUpdating = False
With ThisWorkbook.Sheets("Formulas")
StartgSht = .Index
DataFieldArray = .Range(Cells(100, 1), Cells(147, 8)).Value
End With
For w = 1 to 8
With ThisWorkbook.Sheets(w + StartgSht)
'### what is this used for?
Sht_Select = .Name
For x = LBound(DataFieldArray) To UBound(DataFieldArray)
Pivot_Field = DataFieldArray(x, w)
.PivotTables("PivotTable1").AddDataField
.PivotTables("PivotTable1").PivotFields (Pivot_Field), "Sum of " & Pivot_Field, xlSum
'### you may be able to change the above 2 lines to be...
With .PivotTables("PivotTable1")
.AddDataField
.PivotFields (Pivot_Field), "Sum of " & Pivot_Field, xlSum
End With
Next x
End With
Next w
Application.ScreenUpdating = True
End Sub

--------------------


hth
Rob


--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile: http://www.thecodecage.com/forumz/member.php?userid=333
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=98870