I am newbie in vba and am trying to create a PivotTable
using VBA with excel.
I would like to creat like as below image as input sheet.
I am trying to add row labels of region
, month
, number
, status
and values are value1
, value2
and total
here I am able to set range for pivot, while executing it creates "pivottable" sheet only. not generate any pivot table for sheet1.
My 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 <> -1 Then GoTo nextcode:
item = .SelectedItems(1)
If Right(item, 1) <> "\" Then
item = item & "\"
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
Best Answer
This needs some tidying up but should get you started.
Note the use of Option Explicit so variables have to be declared.
Columns names are as per your supplied workbook.