Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loading Data into an Array | Excel Programming | |||
Problems Loading Large String Array into Array variable | Excel Programming | |||
Loading an Array | Excel Programming | |||
Loading array into list box | Excel Programming | |||
Loading Excel Array from VB Array Faster | Excel Programming |