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