title: Creating Pivot Table taking time at average vales
body: <p>I need help with VBA code.</p>
  <p>I have created a macro to create pivot table automatically. But taking 15 to 20 minutes of time to complete the macro.</p>
  <p>Especially at the point of below code taking half of the time. Review Code below</p>
  <pre><code>    With .PivotFields(&quot;NTS USD ACT Previous Year&quot;)
          .Orientation = xlDataField
          .Position = 1
          .Function = xlAverage
          .NumberFormat = &quot;$#,##;($#,##);-&quot;
          .Caption = &quot;FG NTS PY-Average&quot;
      End With
  </code></pre>
  <p>Requirements:</p>
  <ol>
  <li>Pivot Table should be in Tabular Format.</li>
  <li>Grand Total should be there and when I filter for single &quot;JNJ RM Code&quot; Grand should change.</li>
  <li>Row Sub totals should be turned off.</li>
  <li>Repeat all data labels.</li>
  <li>Adjust Pivot table Column width</li>
  </ol>
  <p>Seeking Advice: Can I have 200,000 data on excel sheet and to use pivot.</p>
  <p>Please do suggest how much data maximum excel can hold with better performance.</p>
  <p>Please review the entire module for your reference.</p>
  <pre><code>Option Explicit
  
  Dim wb As Workbook
  Dim wsData As Worksheet, wsPT As Worksheet
  
  Sub Create_Pivot_Table()
  Dim LR As Long, LC As Byte
  Dim DataRange As Range
  Dim PTCache As PivotCache
  Dim pt As PivotTable
  Dim st As String
  st = Now()
  
  Set wb = ThisWorkbook
  Set wsData = wb.Worksheets(&quot;Data&quot;)
  
  Call Delete_PT_Sheet
  
  With wsData
  LR = .Cells(Rows.Count, &quot;A&quot;).End(xlUp).Row
  LC = .Cells(1, Columns.Count).End(xlToLeft).Column
  
  Set DataRange = .Range(.Cells(1, 1), .Cells(LR, LC))
  
  Set wsPT = wb.Worksheets.Add
  
  wsPT.Name = &quot;Pivot_Data&quot;
  
  Set PTCache = wb.PivotCaches.Create(xlDatabase, DataRange)
  Set pt = PTCache.CreatePivotTable(wsPT.Range(&quot;A2&quot;), &quot;Data&quot;)
  
      With pt
          .ColumnGrand = True
          .HasAutoFormat = True
          .DisplayErrorString = False
          .DisplayNullString = True
          .EnableDrilldown = True
          .ErrorString = &quot;&quot;
          .MergeLabels = False
          .NullString = &quot;&quot;
          .PageFieldOrder = 2
          .PageFieldWrapCount = 0
          .PreserveFormatting = True
          .RowGrand = True
          .PrintTitles = False
          .RepeatItemsOnEachPrintedPage = True
          .TotalsAnnotation = False
          .CompactRowIndent = 1
          .VisualTotals = False
          .InGridDropZones = False
          .DisplayFieldCaptions = True
          .DisplayMemberPropertyTooltips = True
          .DisplayContextTooltips = True
          .ShowDrillIndicators = True
          .PrintDrillIndicators = False
          .AllowMultipleFilters = True
          .SortUsingCustomLists = True
          .DisplayImmediateItems = True
          .FieldListSortAscending = False
          .ShowValuesRow = False
          .RowAxisLayout xlTabularRow
          .RepeatAllLabels xlRepeatLabels
  
          '// Filter(s)
          With .PivotFields(&quot;Region&quot;)
              .Orientation = xlPageField
              .EnableMultiplePageItems = True
              .Subtotals(1) = True
              .Subtotals(1) = False
          End With
          
          '// Row Section (Layer 1)
          With .PivotFields(&quot;JNJ RM Code&quot;)
              .Orientation = xlRowField
              .Position = 1
              .LayoutBlankLine = False
              
              .Subtotals(1) = True
              .Subtotals(1) = False
              .LayoutForm = xlTabular 'xlOutline
              .LayoutCompactRow = True
          End With
          
          '// Row Section (Layer 2)
          With .PivotFields(&quot;Component Description&quot;)
              .Orientation = xlRowField
              .Position = 2
              .LayoutBlankLine = False
              .Subtotals(1) = True
              .Subtotals(1) = False
          End With
                  
          '// Row Section (Layer 3)
          With .PivotFields(&quot;FG Material Code&quot;)
              .Orientation = xlRowField
              .Position = 3
              .LayoutBlankLine = False
              .Subtotals(1) = True
              .Subtotals(1) = False
          End With
           
          '// Row Section (Layer 4)
          With .PivotFields(&quot;FG Material Description&quot;)
              .Orientation = xlRowField
              .Position = 4
              .LayoutBlankLine = False
              .Subtotals(1) = True
              .Subtotals(1) = False
          End With
          
          '// Values Section Layer 2)
          With .PivotFields(&quot;NTS USD ACT Previous Year&quot;)
              .Orientation = xlDataField
              .Position = 1
              .Function = xlAverage
              .NumberFormat = &quot;$#,##;($#,##);-&quot;
              .Caption = &quot;FG NTS PY-Average&quot;
          End With
      
      End With 'PT
  
  End With 'wsData
  
  
  '//Releasing Object Memories
  Set PTCache = Nothing
  Set wsPT = Nothing
  Set DataRange = Nothing
  Set wsData = Nothing
  Set wb = Nothing
  
  ThisWorkbook.Save
  
  'MsgBox (&quot;Completed&quot;)
  
  End Sub
  </code></pre>