Results 1 to 14 of 14

Thread: Pivot tables from VB

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    8

    Pivot tables from VB

    Hi guys,

    Banging my head against the wall for days here. Found a good article for the code to generate a pivot table in a new sheet from data on a sheet. But I want to have a pivot table created from sheet 1 and sheet 2 both to be generated on sheet 3.

    Although the first one works, I can't adapt it to suit what I need it to do.. Anyone able to assist?

    Thanks so much

    Code:
    Sub ByPersonPivot()
    
    'Create the Time report Pivot for the By Person Sheet
    
    'Declare Variables
        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
        
    'Inserting the new sheet
        On Error Resume Next
        Application.DisplayAlerts = False
        Worksheets("PivotTable").Delete
        Sheets.Add After:=ActiveSheet
        ActiveSheet.Name = "PivotTable"
        Application.DisplayAlerts = True
        Set PSheet = Worksheets("PivotTable")
        Set DSheet = Worksheets("Time Report")
        
    'Define Data Range
        LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
        LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
        Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
    
    'Define Pivot Cache
        Set PCache = ActiveWorkbook.PivotCaches.Create _
        (SourceType:=xlDatabase, SourceData:=PRange). _
        CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
        TableName:="ByPersonTimeReport")
        
    'Insert Blank Pivot Table
        Set PTable = PCache.CreatePivotTable _
        (TableDestination:=PSheet.Cells(0, 0), TableName:="ByPersonTimeReport")
        
    'Insert Row Fields
        With ActiveSheet.PivotTables("ByPersonTimeReport").PivotFields("Name Engineer")
        .Orientation = xlRowField
        .Position = 1
    End With
    
        With ActiveSheet.PivotTables("ByPersonTimeReport").PivotFields("Hour Type")
        .Orientation = xlRowField
        .Position = 2
    End With
    
    'Insert Column Fields
        With ActiveSheet.PivotTables("ByPersonTimeReport").PivotFields("Recoverable?")
        .Orientation = xlColumnField
        .Position = 1
    End With
    
    'Insert Data Field
        With ActiveSheet.PivotTables("ByPersonTimeReport").PivotFields("Time2")
        .Orientation = xlDataField
        .Position = 1
        .Function = xlSum
        .NumberFormat = "##.##"
        .Name = "Sum of Time2"
    End With
    
    'Format Pivot
        TableActiveSheet.PivotTables("ByPersonTimeReport").ShowTableStyleRowStripes = TrueActiveSheet.PivotTables("ByPersonTimeReport").TableStyle2 = "PivotStyleMedium9"
    
    End Sub

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Pivot tables from VB

    if the first pivot table is created correctly, then you should be able to reuse the code to create the second

    you can test this to see if it helps at all
    Code:
    Sub ByPersonPivot()
    
    'Create the Time report Pivot for the By Person Sheet
    
    'Declare Variables
        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
        
    'Inserting the new sheet
        On Error Resume Next
        Application.DisplayAlerts = False
        Worksheets("PivotTable").Delete
        Sheets.Add After:=ActiveSheet
        ActiveSheet.Name = "PivotTable"
        Application.DisplayAlerts = True
        Set PSheet = Worksheets("PivotTable")
        Set DSheet = Worksheets("Time Report")
        Set OSheet = Worksheets("2nd sheet")   ' change sheet name to suit
    'Define Data Range
        LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
        LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
        Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
    
    'Define Pivot Cache
        Set PCache = ActiveWorkbook.PivotCaches.Create _
        (SourceType:=xlDatabase, SourceData:=PRange). _
        CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
        TableName:="ByPersonTimeReport")
        
    'Insert Blank Pivot Table
        Set PTable = PCache.CreatePivotTable _
        (TableDestination:=PSheet.Cells(0, 0), TableName:="ByPersonTimeReport")
        
    'Insert Row Fields
        With PSheet.PivotTables("ByPersonTimeReport")
            With .PivotFields("Name Engineer")
                .Orientation = xlRowField
                .Position = 1
            End With
    
            With .PivotFields("Hour Type")
                .Orientation = xlRowField
                .Position = 2
            End With
    
    'Insert Column Fields
            With .PivotFields("Recoverable?")
                .Orientation = xlColumnField
                .Position = 1
            End With
    
    'Insert Data Field
            With .PivotFields("Time2")
                .Orientation = xlDataField
                .Position = 1
                .Function = xlSum
                .NumberFormat = "##.##"
                .Name = "Sum of Time2"
            End With
    
    'Format Pivot
            .PivotTables("ByPersonTimeReport").ShowTableStyleRowStripes = TrueActiveSheet.PivotTables("ByPersonTimeReport").TableStyle2 = "PivotStyleMedium9"
        End With
    
    
    ' 2nd pivot table
    'Define Data Range
        LastRow = OSheet.Cells(Rows.Count, 1).End(xlUp).Row
        LastCol = OSheet.Cells(1, Columns.Count).End(xlToLeft).Column
        Set PRange = OSheet.Cells(1, 1).Resize(LastRow, LastCol)
    
    'Define Pivot Cache    change target address else it will overwrite the first table
        Set PCache = ActiveWorkbook.PivotCaches.Create _
        (SourceType:=xlDatabase, SourceData:=PRange). _
        CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
        TableName:="2nd pivot table")
        
    'Insert Blank Pivot Table
        Set PTable = PCache.CreatePivotTable _
        (TableDestination:=PSheet.Cells(0, 0), TableName:="2nd pivot table")
        
    'Insert Row Fields
        With PSheet.PivotTables("2nd pivot table")
            With .PivotFields("Name Engineer")
                .Orientation = xlRowField
                .Position = 1
            End With
    
            With .PivotFields("Hour Type")
                .Orientation = xlRowField
                .Position = 2
            End With
    
    'Insert Column Fields
            With .PivotFields("Recoverable?")
                .Orientation = xlColumnField
                .Position = 1
            End With
    
    'Insert Data Field
            With .PivotFields("Time2")
                .Orientation = xlDataField
                .Position = 1
                .Function = xlSum
                .NumberFormat = "##.##"
                .Name = "Sum of Time2"
            End With
    
    'Format Pivot
            .ShowTableStyleRowStripes = TrueActiveSheet.PivotTables("2nd pivot table").TableStyle2 = "PivotStyleMedium9"
        End With
    
    
    End Sub
    i have not tested the changes i made to your code nor the additional code i put to create the 2nd table
    you will probably need to change the names of all the pivot fields as well as the source worksheet and pivot table, also the formatting of pivot fields will need to be modified as required, make sure to find some blank area as the target for the 2nd table
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    8

    Re: Pivot tables from VB

    Thanks, I will give this a shot now.

    I am having some "targeting" issues with it. The first pivot generates in B2 however I need this to be A1, The 2nd pivot would be perfect in F1

  4. #4

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    8

    Re: Pivot tables from VB

    Located the Table Destination adjustment. Copied over to a new module for the 2nd pivot as it uses a different DSheet so unable to use the same variables on the first pivot.. Having an error with the Pivot Cache now though...

    Runtime 1004
    Application-defined or object-defined error

    Code:
    Sub ByPersonPayrollPivot()
    
    'Create the Time report Pivot for the By Person Sheet
    
    'Declare Variables
        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
        
    'Define Pivot sheet and Data Sheet
        Set PSheet = Worksheets("ByPerson")
        Set DSheet = Worksheets("Payroll")
        
    'Define Data Range
        LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
        LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
        Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
    
    'Define Pivot Cache
        Set PCache = ActiveWorkbook.PivotCaches.Create _
        (SourceType:=xlDatabase, SourceData:=PRange). _
        CreatePivotTable(TableDestination:=PSheet.Cells(1, 9), _
        TableName:="ByPersonPayroll")
        
    'Insert Blank Pivot Table
        Set PTable = PCache.CreatePivotTable _
        (TableDestination:=PSheet.Cells(1, 9), TableName:="ByPersonPayroll")
        
    'Insert Row Fields
        With ActiveSheet.PivotTables("ByPersonPayroll").PivotFields("Engineer")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    'Insert Data Field
        With ActiveSheet.PivotTables("ByPersonPayroll").PivotFields("BASIC")
        .Orientation = xlDataField
        .Position = 1
        .Function = xlSum
        .NumberFormat = "##.##"
        .Name = "Sum of Basic"
    End With
    
    'Insert Data Field
        With ActiveSheet.PivotTables("ByPersonpayroll").PivotFields("Total OT")
        .Orientation = xlDataField
        .Position = 1
        .Function = xlSum
        .NumberFormat = "##.##"
        .Name = "Sum of Total OT"
    End With
    
    'Format Pivot
        TableActiveSheet.PivotTables("ByPersonPayroll").ShowTableStyleRowStripes = TrueActiveSheet.PivotTables("ByPersonPayoll").TableStyle2 = "PivotStyleMedium9"
    
    End Sub

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Pivot tables from VB

    i am not sure what is causing your problem, but it would appear that you are trying to create 2 pivot tables at the same cell, which could possibly be an issue
    'Define Pivot Cache
    Set PCache = ActiveWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=PRange). _
    CreatePivotTable(TableDestination:=PSheet.Cells(1, 9), _
    TableName:="ByPersonPayroll")

    'Insert Blank Pivot Table
    Set PTable = PCache.CreatePivotTable _
    (TableDestination:=PSheet.Cells(1, 9), TableName:="ByPersonPayroll")
    both lines of code appear to create a pivot table from the same pivot cache, at the same location


    did you try creating the second pivot table before the original, just to see if it works that way?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    8

    Re: Pivot tables from VB

    Yeah deleted the destination sheet then recreated before running the 2nd Pivot and get the error I described in my last reply.

    Can a second pivot cache be created or can we refresh/delete the old one when the 2nd macro is run?

    The location of the pivot tables should be (1, 1) and (1, 9) respectively so plenty of space without them overlapping

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Pivot tables from VB

    can you post some sample workbook (zip first), that demonstrates the problem, for testing?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    8

    Re: Pivot tables from VB

    TestExcelPivots.zip

    I've attached it now. I put the pivot table design in a tab so its clear what I'm aiming to achieve pivot table wise.

    Thanks very much for your help.

  9. #9

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    8

    Re: Pivot tables from VB

    I don't suppose anyone had a chance to take a look at this? Been sat trying all day again today but not getting anywhere.

    Thanks in advance.

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Pivot tables from VB

    no chance last night, computer playing up
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  11. #11

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    8

    Re: Pivot tables from VB

    Feels so close.. Doing it in 2 separate macros which are almost the same but with the data source, rows/columns adjusted, the first pivot generates fine, when running the 2nd macro to create it in the same sheet it generates the table but brings back the mismatch runtime error (see attached image). Problem with the code in the debugger is highlighted below..

    Code:
    Sub ByPersonPayrollPivot()
    
    'Create the Payroll Pivot for the By Person Sheet
    
    'Declare Variables
        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
        
    'Inserting the new sheet
        ActiveSheet.Name = "ByPerson"
        Set PSheet = Worksheets("ByPerson")
        Set DSheet = Worksheets("Payroll")
        
    'Define Data Range
        LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
        LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
        Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
        
    'Define Pivot Cache
        Set PCache = ActiveWorkbook.PivotCaches.Create _
        (SourceType:=xlDatabase, SourceData:=PRange). _
        CreatePivotTable(TableDestination:=PSheet.Cells(1, 9), _
        TableName:="ByPersonPayroll")
        
    'Insert Blank Pivot Table
        Set PTable = PCache.CreatePivotTable _
        (TableDestination:=PSheet.Cells(1, 9), TableName:="ByPersonPayroll")
    
    'Insert Row Fields
        With ActiveSheet.PivotTables("ByPersonPayroll").PivotFields("Engineer")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    'Insert Data Field
        With ActiveSheet.PivotTables("ByPersonPayroll").PivotFields("BASIC")
        .Orientation = xlDataField
        .Position = 1
        .Function = xlSum
        .NumberFormat = "##.##"
        .Name = "Sum of Basic"
    End With
    
    'Insert Data Field
        With ActiveSheet.PivotTables("ByPersonpayroll").PivotFields("Total OT")
        .Orientation = xlDataField
        .Position = 1
        .Function = xlSum
        .NumberFormat = "##.##"
        .Name = "Sum of Total OT"
    End With
    
    'Format Pivot
        TableActiveSheet.PivotTables("ByPersonPayroll").ShowTableStyleRowStripes = TrueActiveSheet.PivotTables("ByPersonPayoll").TableStyle2 = "PivotStyleMedium9"
    
    End Sub
    Name:  error1.jpg
Views: 435
Size:  42.7 KB

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Pivot tables from VB

    i tested your code as in module 2, the type mismatch is because a pivot table is created and you are trying to assign it to a pivotcache object, assign to ptable rather than pcache

    the line of code afterwards will also not work, is not needed at all and should be removed, trying to create a 2nd copy of the pivot table, as pointed put in post 5

    it would appear if only one pivot table is on a worksheet it just ignores the errors, but a 2nd pivot on a worksheet must do it all correctly
    it may also help to explicitly delete any pivot table by name, before creating a new one of that name

    personally i would try to avoid all references to activesheet (or active anything), use the ptable object to work with the fields etc

    the last line also has errors, probably should be
    Code:
    'Format Pivot
        ActiveSheet.PivotTables("ByPersonPayroll").ShowTableStyleRowStripes = PivotStyleMedium9
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  13. #13

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    8

    Re: Pivot tables from VB

    Thank you so much for taking the time to look at it. How do I assign to ptable rather than pcache? Also which 2nd line of code?

    Sorry- I'm fairly inexperienced with this stuff...

  14. #14
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Pivot tables from VB

    'Define Pivot Cache
    Set PCache = ActiveWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=PRange). _
    CreatePivotTable(TableDestination:=PSheet.Cells(1, 9), _
    TableName:="ByPersonPayroll")

    'Insert Blank Pivot Table
    Set PTable = PCache.CreatePivotTable _
    (TableDestination:=PSheet.Cells(1, 9), TableName:="ByPersonPayroll")
    change to
    Code:
    'Define Pivot Cache
        Set PTable = ActiveWorkbook.PivotCaches.Create _
        (SourceType:=xlDatabase, SourceData:=PRange). _
        CreatePivotTable(TableDestination:=PSheet.Cells(1, 9), _
        TableName:="ByPersonPayroll")
        
    'Insert Blank Pivot Table
    '   Set PTable = PCache.CreatePivotTable _
    '   (TableDestination:=PSheet.Cells(1, 9), TableName:="ByPersonPayroll")
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width