Loading pivotfields from an array

[QuietMan]

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").AddDataField
ActiveSheet.PivotTables("PivotTable1").PivotFields(Pivot_Field), "Sum of " &
Pivot_Field, xlSum
Nex x
Next w
Application.ScreenUpdating = True
End Sub

 

[broro183]

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