Is there a way of automating pivot tables using VBA?

[Coyotex3]

Hello guys, I would like to create a two separate pivot tables automatically on a new sheet titled "Pivot". The excel workbook will have 3 sheets. One Named "Original" and one Named "Data". I would like to first create a pivot table based on the data in sheet "Original". Then create another pivot table based off the information in sheet "Data". These will be variable ranges.

Ideally it would look like this:

Book1
	A	B	C	D	E	F
1	Order Number	Date Purchased	Amount + Tax	Amount $	Description	Type
2	1	8/23/2022	150	100	Videos	N/A
3	2	8/23/2022	200	200	Hardware	N/A
4	3	8/23/2022	300	300	Software	N/A
5	4	8/23/2022	250	250	Games	N/A
6	5	8/23/2022	300	300	Appliances	N/A
7	6	8/23/2022	400	400	Games	N/A
8	7	8/23/2022	50	50	Software	N/A
9	8	8/23/2022	60	60	Games	N/A
10
11
12
13
14
15
16
17
Original

Book1
	A	B	C	D	E	F	G
1	Order Number	Date Purchased	Amount + Tax	Description	Type
2	1	8/23/2022	150	Videos	N/A
3	2	8/23/2022	200	Hardware	N/A
4	3	8/23/2022	300	Software	N/A
5	4	8/23/2022	250	Games	N/A
6	5	8/23/2022	300	Appliances	N/A
7	6	8/23/2022	400	Games	N/A
8	7	8/23/2022	50	Software	N/A
9	8	8/23/2022	60	Games	N/A
10
11
12
13
Data

Book1
	A	B	C	D	E	F	G
1
2
3	Row Labels	Sum of Amount $				Row Labels	Sum of Amount + Tax
4	1	100				1	150
5	2	200				2	200
6	3	300				3	300
7	4	250				4	250
8	5	300				5	300
9	6	400				6	400
10	7	50				7	50
11	8	60				8	60
12	Grand Total	1660				Grand Total	1710
13
14
15
Pivot

 

[Colo]

Hi,

Here is a sample code for you. Run this code by debugging mode [F8] key would help your understanding.

Sub Sample1()
    Dim pvtCache(1 To 2) As PivotCache
    Dim pvt(1 To 2) As PivotTable

    'Creating a worksheet named Pivot. If it already exists, delete it.
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Pivot").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    Sheets.Add.Name = "Pivot"

    'Creating PivotCaches
    Set pvtCache(1) = ActiveWorkbook.PivotCaches.Create( _
                      SourceType:=xlDatabase, _
                      SourceData:=Sheets("original").Range("A1").CurrentRegion)

    Set pvtCache(2) = ActiveWorkbook.PivotCaches.Create( _
                      SourceType:=xlDatabase, _
                      SourceData:=Sheets("data").Range("A1").CurrentRegion)

    'Creating PivotTables (from PivotCaches)
    Set pvt(1) = pvtCache(1).CreatePivotTable(TableDestination:=Sheets("Pivot").Range("A3"))
    Set pvt(2) = pvtCache(2).CreatePivotTable(TableDestination:=Sheets("Pivot").Range("F3"))

    'Setting fields
    With pvt(1)
        .PivotFields("Order Number").Orientation = xlRowField    'row field
        .PivotFields("Amount $").Orientation = xlDataField    'data field
    End With

    With pvt(2)
        .PivotFields("Order Number").Orientation = xlRowField    'row field
        .PivotFields("Amount + Tax").Orientation = xlDataField    'data field
    End With
End Sub

 

[Coyotex3]

Hi,

Here is a sample code for you. Run this code by debugging mode [F8] key would help your understanding.

Sub Sample1()
    Dim pvtCache(1 To 2) As PivotCache
    Dim pvt(1 To 2) As PivotTable

    'Creating a worksheet named Pivot. If it already exists, delete it.
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Pivot").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    Sheets.Add.Name = "Pivot"

    'Creating PivotCaches
    Set pvtCache(1) = ActiveWorkbook.PivotCaches.Create( _
                      SourceType:=xlDatabase, _
                      SourceData:=Sheets("original").Range("A1").CurrentRegion)

    Set pvtCache(2) = ActiveWorkbook.PivotCaches.Create( _
                      SourceType:=xlDatabase, _
                      SourceData:=Sheets("data").Range("A1").CurrentRegion)

    'Creating PivotTables (from PivotCaches)
    Set pvt(1) = pvtCache(1).CreatePivotTable(TableDestination:=Sheets("Pivot").Range("A3"))
    Set pvt(2) = pvtCache(2).CreatePivotTable(TableDestination:=Sheets("Pivot").Range("F3"))

    'Setting fields
    With pvt(1)
        .PivotFields("Order Number").Orientation = xlRowField    'row field
        .PivotFields("Amount $").Orientation = xlDataField    'data field
    End With

    With pvt(2)
        .PivotFields("Order Number").Orientation = xlRowField    'row field
        .PivotFields("Amount + Tax").Orientation = xlDataField    'data field
    End With
End Sub

Thank you very much. This works perfectly!