title: How to create pivot table using vba
body: <p>I am newbie in vba and am trying to create a <code>PivotTable</code> using VBA with excel.</p>
  
  <p>I would like to creat like as below image as input sheet.</p>
  
  <p><a href="https://i.stack.imgur.com/RwF4S.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/RwF4S.png" alt="Image of data"></a></p>
  
  <p>I am trying to add row labels of <code>region</code>, <code>month</code>, <code>number</code>, <code>status</code> and values are <code>value1</code>, <code>value2</code> and <code>total</code> here I am able to set range for pivot, while executing it creates "pivottable" sheet only. not generate any pivot table for sheet1.</p>
  
  <p><strong>My Code</strong>:</p>
  
  <pre><code>Option Explicit
  
  Public Sub Input_File__1()
  
  ThisWorkbook.Sheets(1).TextBox1.Text = Application.GetOpenFilename()
  
  End Sub
  
  '======================================================================
  
  Public Sub Output_File_1()
  
  Dim get_fldr, item As String
  Dim fldr As FileDialog
  
  Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
  With fldr
      .AllowMultiSelect = False
      If .Show &lt;&gt; -1 Then GoTo nextcode:
  
      item = .SelectedItems(1)
      If Right(item, 1) &lt;&gt; "\" Then
          item = item &amp; "\"
      End If
  End With
  
  nextcode:
  get_fldr = item
  Set fldr = Nothing
  ThisWorkbook.Worksheets(1).TextBox2.Text = get_fldr
  
  End Sub
  
  '======================================================================
  
  Public Sub Process_start()
  
  Dim Raw_Data_1, Output As String
  Dim Raw_data, Start_Time As String
  Dim PSheet As Worksheet
  Dim DSheet As Worksheet
  Dim PCache As PivotCache
  Dim PTable As PivotTable
  Dim PRange As Range
  Dim LastRow As Long
  Dim LastCol As Long
  
  Start_Time = Time()
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  Raw_Data_1 = ThisWorkbook.Sheets(1).TextBox1.Text
  Output = ThisWorkbook.Sheets(1).TextBox2.Text
  
  Workbooks.Open Raw_Data_1: Set Raw_data = ActiveWorkbook
  Raw_data.Sheets("Sheet1").Activate
  
  On Error Resume Next
  
  'Worksheets("Sheet1").Delete
  Sheets.Add before:=ActiveSheet
  ActiveSheet.Name = "Pivottable"
  
  Application.DisplayAlerts = True
  
  Set PSheet = Worksheets("Pivottable")
  Set DSheet = Worksheets("Sheet1")
  LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
  LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).coloumn
  Set PRange = DSheet.Range("A1").CurrentRegion
  
  Set PCache = ActiveWorkbook.PivotCaches.Create_(SourceType:=xlDatabase, SourceData:=PRange)
  
  Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="PRIMEPivotTable")
  
  With PTable.PivotFields("Region")
      .Orientation = xlRowField
      .Position = 1
  End With
  </code></pre>