Create Pivot table thru VBA

I think recording macro to create Pivot Table is not a good idea. First problem will appear when you  will try to add second Pivot Table – is not possible with the same name.

I’ve searched internet and found few sites with code examples with VBA Pivot Table creation.

Source:
https://www.thespreadsheetguru.com/blog/2014/9/27/vba-guide-excel-pivot-tables
https://www.mrexcel.com/forum/excel-questions/672212-vba-macro-generate-pivot-table-display-sum-field-instead-count.html#post3330709
https://answers.microsoft.com/en-us/msoffice/forum/msoffice_access-mso_other/unable-to-set-the-function-property-of-the-pivot/73aeead5-d9a9-4ddc-adbb-fa2605e5a8b6?auth=1
http://www.contextures.com/excel-vba-pivot-format-macro.html
https://stackoverflow.com/questions/24170895/pivottable-do-not-show-subtotals

' Source:
' https://www.thespreadsheetguru.com/blog/2014/9/27/vba-guide-excel-pivot-tables
' https://www.mrexcel.com/forum/excel-questions/672212-vba-macro-generate-pivot-table-display-sum-field-instead-count.html#post3330709
' https://answers.microsoft.com/en-us/msoffice/forum/msoffice_access-mso_other/unable-to-set-the-function-property-of-the-pivot/73aeead5-d9a9-4ddc-adbb-fa2605e5a8b6?auth=1
' http://www.contextures.com/excel-vba-pivot-format-macro.html
' https://stackoverflow.com/questions/24170895/pivottable-do-not-show-subtotals

Public Sub CreatePivot()

Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt, PivotSheet, TempName  As String
Dim SrcData As String
Dim wiersz, SheetsCount As Long
Dim pvtFld As PivotField
Dim CodeJump, Godzina, ap, DataDzisiejsza
Dim ws As Worksheet

' ---------------------------------------------------------------------------
' ADDING VALUES TO VARIABLES

Workbooks("FileName).Sheets("SheetName").Select
wiersz = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
DataDzisiejsza = Format(Now, "yymmdd")
Godzina = Format(Time(), "hhmmss")
ap = "'"
TempName = "PivotZakres" & Godzina

' ---------------------------------------------------------------------------
' PIVOT INITIAL SETTINGS

Set ws = ActiveSheet					'Determine the data range you want to pivot
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:=TempName, RefersToR1C1:= _
"='" & ws.Name & ap & "!R1C1:R" & wiersz & "C20" 
Set sht = Sheets.Add					'Create a new worksheet
StartPvt = sht.Name & "!" & _ 
sht.Range("A3").Address(ReferenceStyle:=xlR1C1)		'Where do you want Pivot Table to start?
Set pvtCache = ActiveWorkbook.PivotCaches.Create( _		
    SourceType:=xlDatabase, _
    SourceData:=TempName)				'Create Pivot Cache from Source Data

  Set pvt = pvtCache.CreatePivotTable( _
    TableDestination:=StartPvt, _
    TableName:="P" & Godzina)
 
' ---------------------------------------------------------------------------
' CREATE PIVOT

With pvt
    ' INITIAL SETTINGS
    .InGridDropZones = True
    .RowAxisLayout xlTabularRow
    '.TableStyle2 = ""
    .DisplayContextTooltips = False
    .ShowDrillIndicators = False
    .RepeatAllLabels xlRepeatLabels
    .NullString = ""

    ' ROWS AREA   
    .PivotFields("ColumnName1").Orientation = xlRowField
    .PivotFields("ColumnName2").Orientation = xlRowField

    ' DATA AREA
    With .PivotFields("ColumnName3")
        .Orientation = xlDataField 
        .Caption = "Coulmn3NewName"
        .Function = xlSum 
    End With

   With .PivotFields("ColumnName4")
        .Orientation = xlDataField 
        .Caption = "Coulmn4NewName"
        .Function = xlCount
    End With

    ' DELETING SUBTOTALS 
    For Each pvtFld In .PivotFields
        If pvtFld = "Values" Then
            'Omniecie beldu
        Else
            pvtFld.Subtotals(1) = True
            pvtFld.Subtotals(1) = False
        End If
    Next pvtFld
End With

' ---------------------------------------------------------------------------
' NAMING SHEET WITH PIVOT
    
With Workbooks(FileName)
    ' Searching if exists
    For SheetsCount = 1 To .Sheets.Count
        If ActiveSheet.Name = "Pivot-" & DataDzisiejsza Then
            ActiveSheet.Name = "Pivot-" & DataDzisiejsza & Godzina
            GoTo CodeJump
        Else
            ' Nothing happened
        End If
    Next SheetsCount
    ActiveSheet.Name = "Pivot-" & DataDzisiejsza
    
CodeJump:

    PivotSheet = ActiveSheet.Name
    .Sheets(PivotSheet).Move After:=.Sheets(Workbooks(OrginalFileName).Sheets.Count)
End With

End Sub

 

45 thoughts on “Create Pivot table thru VBA

  1. Pingback: white leaf

  2. Pingback: best dumps shop online

  3. Pingback: cc cvv dumps

  4. Pingback: mơ thấy đi chợ

  5. Pingback: mơ thấy đá bóng đánh con gì

  6. Pingback: nằm mơ thấy phật bà quan âm

  7. Pingback: nằm mơ thấy mình thắp hương

  8. Pingback: mơ thấy nhẫn vàng đánh con gì

  9. Pingback: mơ thấy giày dép đánh con gì

  10. Pingback: nằm mơ thấy cháy rừng

  11. Pingback: cod warzone cheats xbox

  12. Pingback: mơ thấy ăn ổi

  13. Pingback: mơ thấy chìa khóa

  14. Pingback: mơ thấy chim bồ câu đánh con gì

  15. Pingback: mơ thấy con sâu

  16. Pingback: mơ cãi nhau đánh con gì

  17. Pingback: Red Bull Energy Drink

  18. Pingback: mơ cá trê đánh con gì

  19. Pingback: mơ thấy thầy bói

  20. Pingback: nằm mơ thấy hoa sen

  21. Pingback: nằm mơ thấy ngựa đánh số gì

  22. Pingback: mơ cá sấu đánh con gì

  23. Pingback: mơ thấy con trâu đánh đề con gì

  24. Pingback: nằm mơ thấy bướm

  25. Pingback: mơ thấy sếp cũ

  26. Pingback: mơ thấy bếp lửa cháy

  27. Pingback: nằm mơ thấy hôn môi

  28. Pingback: anonymous sell files on tor

  29. Pingback: nằm mơ thấy ốc đánh con gì

  30. Pingback: bola gelinding

  31. Pingback: judi online

  32. Pingback: 커즐

  33. Pingback: 먹튀검증

  34. Pingback: meja qq

  35. Pingback: Fixed Today Plumbing Concord

  36. Pingback: best slowed songs

  37. Pingback: redeyecheats esea

  38. Pingback: FSU cornhole boards

  39. Pingback: dumps and cvv for sale

  40. Pingback: ตู้แปลภาษา

  41. Pingback: https://home-remodeling-directory.com/

  42. Pingback: Mobiele airco

  43. Pingback: mơ thấy voi

  44. Pingback: mơ thấy con đỉa

  45. Pingback: mơ thấy ngựa đánh con gì

Leave a Reply

Your email address will not be published. Required fields are marked *