To create a pivot table from data set with VBA

[TopLearner]

Hello there,



I hope you are well.



I have data on sheet “Sheet1”

I would like to create a pivot table on a separate tab called "Pivot" with this data using VBA. I have already tried but without success.

"Sheet1" tab

"Pivot" tab

I would like to present the table in tabular form. I get stuck with the code.

Dim ws As Worksheet

Dim newws As Worksheet

Dim PTcache As PivotCache

Dim PT As PivotTable

Dim Pivrang As String

Dim Startpiv As String





Set ws = ActiveSheet

Pivrang = ws.Activate. Address(ReferenceStyle:=xlR1C1)

Cells.Select



Set news = Sheets.Add



Startpiv = ws.Range (“A3”). Address(ReferenceStyle:=xlR1C1)



Set pvtCache = ActiveWorkbook.PivotCaches.Create( _

SourceType:=xlDatabase, _

SourceData:=SrcData)



Set pvt = pvtCache.CreatePivotTable( _

TableDestination:=StartPvt, _

TableName:="PivotTable1")

I am hoping you can help me by providing the code.

Thanks
Sanchez

 

[Coyotex3]

Hi, TopLearner, where does your data start on Sheet1?

The following code below assumes that your data is starting on Range("A1") like it does on my sheet(see the link below for an example as to what my data looked like).

Sub TopLearner()
    Dim pvtCache As PivotCache
    Dim pvt As PivotTable


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

 

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

    'Setting fields
    With pvt
        .PivotFields("Employee First Name").Orientation = xlRowField
        .PivotFields("Employee Last Name").Orientation = xlRowField
        .PivotFields("Department Name").Orientation = xlRowField
        .PivotFields("Pay Code Description").Orientation = xlRowField
        .PivotFields("Hours").Orientation = xlDataField    'data field
    End With

End Sub

Have a look at this thread. I posted a similar question in recently. Hope this helps!

Is there a way of automating pivot tables using VBA?

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...

www.mrexcel.com

 

[TopLearner]

Hi, TopLearner, where does your data start on Sheet1?

The following code below assumes that your data is starting on Range("A1") like it does on my sheet(see the link below for an example as to what my data looked like).

Sub TopLearner()
    Dim pvtCache As PivotCache
    Dim pvt As PivotTable


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

 

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

    'Setting fields
    With pvt
        .PivotFields("Employee First Name").Orientation = xlRowField
        .PivotFields("Employee Last Name").Orientation = xlRowField
        .PivotFields("Department Name").Orientation = xlRowField
        .PivotFields("Pay Code Description").Orientation = xlRowField
        .PivotFields("Hours").Orientation = xlDataField    'data field
    End With

End Sub

Have a look at this thread. I posted a similar question in recently. Hope this helps!

Is there a way of automating pivot tables using VBA?

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...

www.mrexcel.com

Thank you Coyotex3

My data starts in A1 on Sheet1 and takes several cells whose number varies depending of the month the data belongs to

SourceData:=Sheets("sheet1").Range("A1").CurrentRegion)

By writing the above line, does it mean I am taking all the cells that are not blank on Sheet1?

Thanks again
Sanchez

 

[TopLearner]

Hi, TopLearner, where does your data start on Sheet1?

The following code below assumes that your data is starting on Range("A1") like it does on my sheet(see the link below for an example as to what my data looked like).

Sub TopLearner()
    Dim pvtCache As PivotCache
    Dim pvt As PivotTable


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

 

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

    'Setting fields
    With pvt
        .PivotFields("Employee First Name").Orientation = xlRowField
        .PivotFields("Employee Last Name").Orientation = xlRowField
        .PivotFields("Department Name").Orientation = xlRowField
        .PivotFields("Pay Code Description").Orientation = xlRowField
        .PivotFields("Hours").Orientation = xlDataField    'data field
    End With

End Sub

Have a look at this thread. I posted a similar question in recently. Hope this helps!

Is there a way of automating pivot tables using VBA?

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...

www.mrexcel.com

Hi again,

Would it be possible to add to the macro the creation of the sheet "Pivot" as it is not supposed to be manually created?
Also, how I could do so that the different fields "Employee First Name", "Employee Last Name", "Department Name", "Pay Code Description" are presented in a tabular layout?

Thanks
Sanchez

 

[SpillerBD]

Thank you Coyotex3

My data starts in A1 on Sheet1 and takes several cells whose number varies depending of the month the data belongs to

SourceData:=Sheets("sheet1").Range("A1").CurrentRegion)

By writing the above line, does it mean I am taking all the cells that are not blank on Sheet1?

Thanks again
Sanchez

Current Region is like pressing CTRL + * to select a range.

 

[Coyotex3]

Hi TopLearner, give this one a try to see if it does what you need.

Sub TopLearner1()
    Dim pvtCache As PivotCache
    Dim pvt As PivotTable

    Sheets.Add.Name = "Pivot"          ' Create a new worksheet called "Pivot"
    'Creating PivotCaches
    Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
                      SourceType:=xlDatabase, _
                      SourceData:=Sheets("sheet1").Range("A3").CurrentRegion)

   

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

    'Setting fields
    With pvt
        .PivotFields("Employee First Name").Orientation = xlRowField    'row field
        .PivotFields("Employee Last Name").Orientation = xlRowField
        .PivotFields("Department Name").Orientation = xlRowField
        .PivotFields("Pay Code Description").Orientation = xlRowField
        .PivotFields("Hours").Orientation = xlDataField    'data field
    End With

    With Sheets("Pivot")
        pvt(1).RowAxisLayout xlTabularRow
    End With

End Sub

 

[TopLearner]

Hi TopLearner, give this one a try to see if it does what you need.

Sub TopLearner1()
    Dim pvtCache As PivotCache
    Dim pvt As PivotTable

    Sheets.Add.Name = "Pivot"          ' Create a new worksheet called "Pivot"
    'Creating PivotCaches
    Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
                      SourceType:=xlDatabase, _
                      SourceData:=Sheets("sheet1").Range("A3").CurrentRegion)

  

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

    'Setting fields
    With pvt
        .PivotFields("Employee First Name").Orientation = xlRowField    'row field
        .PivotFields("Employee Last Name").Orientation = xlRowField
        .PivotFields("Department Name").Orientation = xlRowField
        .PivotFields("Pay Code Description").Orientation = xlRowField
        .PivotFields("Hours").Orientation = xlDataField    'data field
    End With

    With Sheets("Pivot")
        pvt(1).RowAxisLayout xlTabularRow
    End With

End Sub

Many thanks. Only two questions.
If I want the pay code description to be in the filters field, would I have to type in xlFilterField?

Also, by default subtotals is automatic, how could I set none?

Thanks
Sanchez