dcsimg
Results 1 to 19 of 19

Thread: Excel 2013. Creating a Pivot Table based on data in another worksheet using VBA.

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Feb 2018
    Posts
    16

    Excel 2013. Creating a Pivot Table based on data in another worksheet using VBA.

    Hi again,

    I hope you can help. I am trying to create a Pivot Table using Excel 2013 and VBA. The code appears to be error free but does not produce anything.

    I use Msgbox (stripped out) to map the journey through the code and the data seems to be as I expect. Can anyone give me any guidance?

    Thanks.

    The full code is:

    Debug.Print

    Dim Debugger As String
    Debugger = "On"

    'PVT is abbreviation for Pivot Table

    'Define Workbook name
    Dim HandoverPVTWorkbook As Workbook
    Set HandoverPVTWorkbook = ActiveWorkbook

    'Define Worksheet that is generated to contain the pivot table.
    Dim HandoverPVTWorksheet As Worksheet
    Set HandoverPVTWorksheet = ActiveSheet

    'Define Worksheet that is used to hold the source data
    Dim HandoverPVTSourceData As Worksheet
    Set HandoverPVTSourceData = Worksheets("XXXXXHandoverReport")

    'Define the Source Data range
    Dim HandoverPVTLastRow As Integer 'Define the last row of the Source Data
    HandoverPVTLastRow = HandoverPVTSourceData.Cells(Rows.Count, 1).End(xlUp).Row 'Calculates the last used row in the worksheet containing the source data

    Dim HandoverPVTRangeString As String
    HandoverPVTRangeString = "A4:H" & HandoverPVTLastRow

    'Get Source Data
    Set HandoverPVTRangeData = HandoverPVTSourceData.Range(HandoverPVTRangeString)

    'Get Pivot Table Ranges
    Dim HandoverXXXXXRangeString As String
    HandoverXXXXXRangeString = "K4:R" & HandoverPVTLastRow

    'Delete Last night Pivot Tablea and Caches
    Dim xPt As PivotTable
    Dim xWs As Worksheet
    Dim xPc As PivotCache
    Application.ScreenUpdating = False
    For Each xWs In ActiveWorkbook.Worksheets
    For Each xPt In xWs.PivotTables
    xPt.PivotCache.MissingItemsLimit = xlMissingItemsNone
    Next xPt
    Next xWs
    For Each xPc In ActiveWorkbook.PivotCaches
    On Error Resume Next
    xPc.Refresh
    Next xPc
    Application.ScreenUpdating = True


    'Create Pivot Table Caches
    Dim HandoverPVTCacheXXXXX As PivotCache
    Set HandoverPVTCacheXXXXX = HandoverWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=HandoverPVTSourceData.Range(HandoverPVTRangeData), Version:=xlPivotTableVersion15)

    'Create Pivot Tables

    Dim HandoverPVTXXXXX As PivotTable
    Set HandoverPVTXXXXX = HandoverPVTWorksheet.PivotTables.Add(PivotCache:=HandoverPVTCacheXXXXX, TableDestination:=Range("K4"), TableName:="HandoverPVTXXXXX")

    'Define Rows
    With ActiveSheet.PivotTables("HandoverPVTXXXXX")
    .PivotFields ("AAAAA")
    .Orientation = xlRowField
    .Position = 1
    End With

    With ActiveSheet.PivotTables("HandoverPVTXXXXX")
    .PivotFields ("BBBBB")
    .Position = 2
    End With

    With ActiveSheet.PivotTables("HandoverPVTXXXXX")
    .PivotFields ("CCCCC")
    .Orientation = xlRowField
    .Position = 3
    End With

    With ActiveSheet.PivotTables("HandoverPVTXXXXX")
    .PivotFields ("DDDDD")
    .Orientation = xlRowField
    .Position = 4
    End With

    With ActiveSheet.PivotTables("HandoverPVTXXXXX")
    .PivotFields ("EEEEE")
    .Orientation = xlRowField
    .Position = 5
    End With

    With ActiveSheet.PivotTables("HandoverPVTXXXXX")
    .PivotFields ("FFFFF")
    .Orientation = xlRowField
    .Position = 6
    End With

    With ActiveSheet.PivotTables("HandoverPVTXXXXX")
    .PivotFields ("GGGGG")
    .Orientation = xlRowField
    .Position = 7
    .PivotFilters.Add Type:=xlCaptionContains, Value1:="XXXXX"
    End With

    With ActiveSheet.PivotTables("HandoverPVTXXXXX")
    .AddDataField.PivotFields ("HHHHH"), "Cancels", xlSum
    .Orientation = xlRowField
    .Position = 8
    End With

    'Define the Pivot Table Style
    ActiveSheet.PivotTables("HandoverPVTXXXXX").ShowTableStyleRowStripes = True

    ActiveSheet.PivotTables("HandoverPVTXXXXX").TableStyle2 = "PivotStyleMedium9"

    End

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,724

    Re: Excel 2013. Creating a Pivot Table based on data in another worksheet using VBA.

    can you post a sample workbook? zip first
    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
    Junior Member
    Join Date
    Feb 2018
    Posts
    16

    Re: Excel 2013. Creating a Pivot Table based on data in another worksheet using VBA.

    Hi Westconn1,

    I have attached a zip file with the code and data in it. The contents can go from a handful of rows up to several thousand in any one day.

    The contents of the file are :

    XXXXX Handover Report. Date: 07/03/2018 Handover Time: 18:00

    AAAAA BBBBB CCCCC DDDDD EEEEE FFFFF GGGGG HHHHH
    Person A Zone 01 Location 0123 Location Name 0123 YYYYY issue Source 1 YYYYY Preventable 6
    Person B Zone 02 Location 0124 Location Name 0124 XXXXX Issue Source 1 XXXXX Preventable 10
    Person C Zone 03 Location 0125 Location Name 0125 YYYYY issue Source 1 YYYYY Preventable 7
    Person C Zone 03 Location 0125 Location Name 0125 XXXXX Issue Source 1 XXXXX Preventable 13
    Person C Zone 03 Location 0126 Location Name 0126 XZZZZZ Issue Source 1 ZZZZZ 14
    Person C Zone 03 Location 0126 Location Name 0126 YYYYY issue Source 1 YYYYY Preventable 15
    Person C Zone 03 Location 0126 Location Name 0126 XXXXX Issue Source 1 XXXXX Preventable 12
    Person D Zone 04 Location 0127 Location Name 0127 YYYYY issue Source 2 YYYYY Preventable 4
    Person E Zone 05 Location 0128 Location Name 0128 XXXXX Issue Source 1 XXXXX Preventable 3
    Person F Zone 06 Location 0129 Location Name 0129 XXXXX Issue Source 2 XXXXX Preventable 8
    Person G Zone 07 Location 0130 Location Name 0130 YYYYY issue Source 1 YYYYY Preventable 1

    Once I adjusted the data, I tried running the code for the last time before sending and got a "Run time error 1004 - Range of object "_Worksheet" failed " message. I changed the code to anonymise but was careful to ensure that the code was robust - clearly must have made a change. The original code goes from beginning to end without fail except that the worksheet remains blanks.

    Essentially, what I am looking for is a pivot table based on columns A to H (rows 4 - 14). The pivot table is to be filtered so only those in column G where the contents of the cell is "XXXXX Preventable" are displayed.

    Many thanks for taking the time to look.

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Feb 2018
    Posts
    16

    Re: Excel 2013. Creating a Pivot Table based on data in another worksheet using VBA.

    Quote Originally Posted by westconn1 View Post
    can you post a sample workbook? zip first
    Zip file attached
    Attached Files Attached Files

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,724

    Re: Excel 2013. Creating a Pivot Table based on data in another worksheet using VBA.

    i made a few changes to the code, it now creates a pivottable in the desired location, but i have no idea if the result is as expected, i would see it as some problems need fixing, but pivot tables are not really my thing

    Code:
    Sub XXXXXHandoverReportPivotTable()
    
    Debug.Print
    
    Dim Debugger As String
    Debugger = "On"
    
    'PVT is abbreviation for Pivot Table
    
    'Define Workbook name
        Dim HandoverPVTWorkbook As Workbook
        Set HandoverPVTWorkbook = ThisWorkbook   ' much more robust than active workbook
    
    'Define Worksheet that is generated to contain the pivot table.
        Dim HandoverPVTWorksheet As Worksheet
        Set HandoverPVTWorksheet = Sheets(1)  ' the sheet name was different to that used in your code
    
    'Define Worksheet that is used to hold the source data
        Dim HandoverPVTSourceData As Worksheet
        Set HandoverPVTSourceData = Worksheets("XXXXXHandoverReport")
    
    'Define the Source Data range
        Dim HandoverPVTLastRow As Integer 'Define the last row of the Source Data
        HandoverPVTLastRow = HandoverPVTSourceData.Cells(Rows.Count, 1).End(xlUp).Row 'Calculates the last used row in the worksheet containing the source data
    
        Dim HandoverPVTRangeString As String
        HandoverPVTRangeString = "A4:H" & HandoverPVTLastRow
        
        If Debugger = "On" Then
            MsgBox "Range " & HandoverPVTRangeString
            End If
    
    'Get Source Data
        Dim HandoverPVTRangeData
        Set HandoverPVTRangeData = HandoverPVTSourceData.Range(HandoverPVTRangeString)
    
    'Get Pivot Table Ranges
        Dim HandoverXXXXXPreventableRangeString As String
        HandoverXXXXXPreventableRangeString = "K4:R" & HandoverPVTLastRow
     
    'Delete Last night Pivot Tablea and Caches
        Dim xPt As PivotTable
        Dim xWs As Worksheet
        Dim xPc As PivotCache
        Application.ScreenUpdating = False
        For Each xWs In ActiveWorkbook.Worksheets
            For Each xPt In xWs.PivotTables
                xPt.PivotCache.MissingItemsLimit = xlMissingItemsNone
            Next xPt
        Next xWs
        For Each xPc In ActiveWorkbook.PivotCaches
            On Error Resume Next
            xPc.Refresh
        Next xPc
        Application.ScreenUpdating = True
    
    
    'Create Pivot Table Caches
        Dim HandoverPVTCacheXXXXXPreventable As PivotCache
    '    Set HandoverPVTCacheXXXXXPreventable = HandoverPVTWorksheet.PivotTables(1).PivotCaches.Create(SourceType:=xlDatabase, SourceData:=HandoverPVTSourceData.Range(HandoverPVTRangeData), Version:=xlPivotTableVersion15)
        Set HandoverPVTCacheXXXXXPreventable = HandoverPVTWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=HandoverPVTRangeData, Version:=xlPivotTableVersion15)
    'Create Pivot Tables
        
        Dim HandoverPVTXXXXXPreventable As PivotTable
    '    Set HandoverPVTXXXXXPreventable = HandoverPVTWorksheet.PivotTables.Add(PivotCache:=HandoverPVTCacheXXXXXPreventable, TableDestination:=Range("K4"), TableName:="HandoverPVTXXXXXPreventable")
        Set HandoverPVTXXXXXPreventable = HandoverPVTWorksheet.PivotTables.Add(HandoverPVTCacheXXXXXPreventable, HandoverPVTWorksheet.Range("k4"), "HandoverPVTXXXXXPreventable")
    'Define Rows
        With HandoverPVTWorksheet.PivotTables("HandoverPVTXXXXXPreventable")
        
            .PivotFields ("AAAAA")
            .Orientation = xlRowField
            .Position = 1
            
            .PivotFields ("BBBBB")
            .Position = 2
            
            .PivotFields ("CCCCC")
            .Orientation = xlRowField
            .Position = 3
            
            .PivotFields ("DDDDD")
            .Orientation = xlRowField
            .Position = 4
            
            .PivotFields ("EEEEE")
            .Orientation = xlRowField
            .Position = 5
            
            .PivotFields ("FFFFF")
            .Orientation = xlRowField
            .Position = 6
            
            .PivotFields ("GGGGG")
            .Orientation = xlRowField
            .Position = 7
            .PivotFilters.Add Type:=xlCaptionContains, Value1:="XXXXX Preventable"
            
            .AddDataField.PivotFields ("HHHHH"), "Cancels", xlSum
            .Orientation = xlRowField
            .Position = 8
    
    'Define the Pivot Table Style
            .ShowTableStyleRowStripes = True
            .TableStyle2 = "PivotStyleMedium9"
        End With
    End Sub
    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
    Junior Member
    Join Date
    Feb 2018
    Posts
    16

    Re: Excel 2013. Creating a Pivot Table based on data in another worksheet using VBA.

    Hi Westconn

    Thank you so much for your help, I will test this out and get back.

    It is so frustrating getting 99% there but i guess the 1% is the fun part.

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Feb 2018
    Posts
    16

    Re: Excel 2013. Creating a Pivot Table based on data in another worksheet using VBA.

    Hi Westconn,

    I made the changes but still not generating the pivot table.

    The two changes I made were the following. Have I missed any?

    Set HandoverPVTWorkbook = ThisWorkbook ' much more robust than active workbook
    Set HandoverPVTWorksheet = Sheets(1) ' the sheet name was different to that used in your code

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Feb 2018
    Posts
    16

    Re: Excel 2013. Creating a Pivot Table based on data in another worksheet using VBA.

    Hi Westconn

    I have just spotted a typo error in the workbook name further down the original code. I will dig around there.

    Thanks for the guidance

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,724

    Re: Excel 2013. Creating a Pivot Table based on data in another worksheet using VBA.

    i commented out 2 lines and put replacements below
    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

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Feb 2018
    Posts
    16

    Re: Excel 2013. Creating a Pivot Table based on data in another worksheet using VBA.

    Hi Westconn,

    Yes, I found those when I noticed the commented out lines :-). I have made the adjustments to the original code and have the pivot table area showing for the first time (many thanks). I have just got to see what I am doing wrong with the pivot table itself as that is not showing yet.

    When you got it to work, did you see any data on the pivot table? I am expecting the second, ninth and tenth lines of data to be showing.

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,724

    Re: Excel 2013. Creating a Pivot Table based on data in another worksheet using VBA.

    the contents of the table were too small to really see details
    you could probably zoom the sheet to see better

    try adjusting the created pivot manually to see what needs to be done

    Yes, I found those
    i also changed all the with blocks, relying on the active sheet, into a single one, which could probably be changed again to
    Code:
        With HandoverPVTXXXXXPreventable
    as you already have the object for the pivot 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

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Feb 2018
    Posts
    16

    Re: Excel 2013. Creating a Pivot Table based on data in another worksheet using VBA.

    Hi again,

    Am making a lot of progress thanks to your help.

    I now have the three pivot table blocks being displayed but no content.

    If I click anywhere in the block and look at the field list, I see the content of the reklevant fields rather than the field names, I assume that the code is missing field headers (the names of the fields in the VBA equate with those on the source data.

    Is anything jumping out as to why the indivdual fields are not shown, not even as empty fields (see attached). There are no fields in the grid showing values, filters, rows or columns. The specified fields are not being recognised as pivot fields. I have tried the PivotField property but don't think I have that right.

    Many thanks for your time.

    The current code is :
    'Create Pivot Table Cache
    Dim HandoverPVTCacheXXXXXPreventable As PivotCache
    Set HandoverPVTCacheXXXXXPreventable = HandoverPVTWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=HandoverPVTRangeData, Version:=xlPivotTableVersion15)

    'Create Pivot Tables (W4,is the starting cell for the Pivot Table)
    Dim HandoverPVTXXXXXPreventable As PivotTable
    Set HandoverPVTXXXXXPreventable = HandoverPVTWorksheet.PivotTables.Add(PivotCache:=HandoverPVTCacheXXXXXPreventable, TableDestination:=Range("W4"), TableName:="HandoverPVTXXXXXPreventable")

    'Define Rows (XXXXX Preventable)
    With HandoverPVTWorksheet.PivotTables("HandoverPVTXXXXXPreventable")

    .PivotFields ("AAAAA")
    .Orientation = xlRowField
    .Position = 1

    .PivotFields ("BBBBB")
    .Orientation = xlRowField
    .Position = 2

    .PivotFields ("CCCCC")
    .Orientation = xlRowField
    .Position = 3

    .PivotFields ("DDDDD")
    .Orientation = xlRowField
    .Position = 4

    .PivotFields ("EEEEE")
    .Orientation = xlRowField
    .Position = 5

    .PivotFields ("FFFFF")
    .Orientation = xlRowField
    .Position = 6

    .PivotFields ("GGGGG")
    .Orientation = xlPageField
    .Position = 7
    .PivotFilters.Add Type:=xlCaptionContains, Value1:="XXXXX Preventable"

    .AddDataField.PivotFields ("Cancels")
    .Orientation = xlDataField
    .Function = xlSum
    .Position = 8

    End With
    Attached Images Attached Images  

  13. #13
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,724

    Re: Excel 2013. Creating a Pivot Table based on data in another worksheet using VBA.

    i looked at this again, i now have (i think) a proper pivot table
    i commented out a heap of code that i believe was incorrect anyway
    i found that when the pivot table was created, the object returned was always nothing
    you need to include row 3 from the data to get the headers
    some lines of code that worked the first time errored later
    i had at one point to restart excel as it all stopped working
    i am still not at all sure that the result is as desired, but it is probably a step forward anyway

    Code:
    Sub XXXXXHandoverReportPivotTable()
    
    Debug.Print
    
    Dim Debugger As String
    Debugger = "On"
    
    'PVT is abbreviation for Pivot Table
    
    'Define Workbook name
        Dim HandoverPVTWorkbook As Workbook
        Set HandoverPVTWorkbook = ThisWorkbook   ' much more robust than active workbook
    
    'Define Worksheet that is generated to contain the pivot table.
        Dim HandoverPVTWorksheet As Worksheet
        Set HandoverPVTWorksheet = Sheets(1)  ' the sheet name was different to that used in your code
    
    'Define Worksheet that is used to hold the source data
        Dim HandoverPVTSourceData As Worksheet
        Set HandoverPVTSourceData = Worksheets("XXXXXHandoverReport")
    
    'Define the Source Data range
        Dim HandoverPVTLastRow As Integer 'Define the last row of the Source Data
        HandoverPVTLastRow = HandoverPVTSourceData.Cells(Rows.Count, 1).End(xlUp).Row 'Calculates the last used row in the worksheet containing the source data
    
        Dim HandoverPVTRangeString As String
        HandoverPVTRangeString = "A3:H" & HandoverPVTLastRow
        
        If Debugger = "On" Then
    '        MsgBox "Range " & HandoverPVTRangeString
            End If
    
    'Get Source Data
        Dim HandoverPVTRangeData
        Set HandoverPVTRangeData = HandoverPVTSourceData.Range(HandoverPVTRangeString)
    
    'Get Pivot Table Ranges
        Dim HandoverXXXXXPreventableRangeString As String
        HandoverXXXXXPreventableRangeString = "K4:R" & HandoverPVTLastRow
     
    'Delete Last night Pivot Tablea and Caches
        Dim xPt As PivotTable
        Dim xWs As Worksheet
        Dim xPc As PivotCache
        Application.ScreenUpdating = False
        For Each xWs In ActiveWorkbook.Worksheets
            For Each xPt In xWs.PivotTables
                xPt.PivotCache.MissingItemsLimit = xlMissingItemsNone
            Next xPt
        Next xWs
        For Each xPc In ActiveWorkbook.PivotCaches
            On Error Resume Next
            xPc.Refresh
        Next xPc
        Application.ScreenUpdating = True
    
    
    'Create Pivot Table Caches
        Dim HandoverPVTCacheXXXXXPreventable As PivotCache
    '    Set HandoverPVTCacheXXXXXPreventable = HandoverPVTWorksheet.PivotTables(1).PivotCaches.Create(SourceType:=xlDatabase, SourceData:=HandoverPVTSourceData.Range(HandoverPVTRangeData), Version:=xlPivotTableVersion15)
        Set HandoverPVTCacheXXXXXPreventable = HandoverPVTWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=HandoverPVTRangeData, Version:=xlPivotTableVersion15)
    'Create Pivot Tables
        
        Dim HandoverPVTXXXXXPreventable As PivotTable
    '    Set HandoverPVTXXXXXPreventable = HandoverPVTWorksheet.PivotTables.Add(PivotCache:=HandoverPVTCacheXXXXXPreventable, TableDestination:=Range("K4"), TableName:="HandoverPVTXXXXXPreventable")
        Set HandoverPVTXXXXXPreventable = HandoverPVTWorksheet.PivotTables.Add(HandoverPVTCacheXXXXXPreventable, HandoverPVTWorksheet.Range("k4"), "HandoverPVTXXXXXPreventable")
    'Define Rows
    Set HandoverPVTXXXXXPreventable = HandoverPVTWorksheet.PivotTables(1)
    'Set xPt = HandoverPVTWorksheet.PivotTables(1)
    Dim f As PivotField, i As Integer
    'Dim f2
    'Set f = xPt.PivotFields(1)
    'Set f2 = xPt.PivotFields(2)
    i = 1
        With HandoverPVTXXXXXPreventable
        For Each f In .PivotFields
            f.Position = i
            i = i + 1
            Select Case f.Position
                Case 1 To 6
                    f.Orientation = xlRowField
                Case 7
                    f.Orientation = xlPageField
                    f.PivotFilters.Add2 xlCaptionContains, , "XXXXX Peventable"
                Case 8
                    f.Orientation = xlDataField
                    f.Function = xlSum
            End Select
        Next
        
    '''        .PivotFields ("AAAAA")
    '''        .Orientation = xlRowField
    '''        .Position = 1
    '''
    '''        .PivotFields ("BBBBB")
    '''        .Position = 2
    '''
    '''        .PivotFields ("CCCCC")
    '''        .Orientation = xlRowField
    '''        .Position = 3
    '''
    '''        .PivotFields ("DDDDD")
    '''        .Orientation = xlRowField
    '''        .Position = 4
    '''
    '''        .PivotFields ("EEEEE")
    '''        .Orientation = xlRowField
    '''        .Position = 5
    '''
    '''        .PivotFields ("FFFFF")
    '''        .Orientation = xlRowField
    '''        .Position = 6
    '''
    '''        .PivotFields ("GGGGG")
    '''        .Orientation = xlRowField
    '''        .Position = 7
    '''        .PivotFilters.Add Type:=xlCaptionContains, Value1:="XXXXX Preventable"
    '''
    '''        .AddDataField.PivotFields ("HHHHH"), "Cancels", xlSum
    '''        .Orientation = xlRowField
    '''        .Position = 8
    '''
    ''''Define the Pivot Table Style
    '''        .ShowTableStyleRowStripes = True
    '''        .TableStyle2 = "PivotStyleMedium9"
        End With
    End Sub
    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

  14. #14

    Thread Starter
    Junior Member
    Join Date
    Feb 2018
    Posts
    16

    Re: Excel 2013. Creating a Pivot Table based on data in another worksheet using VBA.

    Hi Westconn,

    Thank you very much for your response.

    I have put the code in as you suggested and was over the moon to see that the pivot table was populated. I will have to look at the formatting and filtering side but I now see data and the field list. I was on the right track but it isn't clear at all in the manuals and examples :-).Thank you so much for your help.

  15. #15
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,724

    Re: Excel 2013. Creating a Pivot Table based on data in another worksheet using VBA.

    i thought one of the members here, more familiar with pivot tables would have stepped in

    i have never worked with pivot tables, so it was all trial and lots of errors, several things that should have just worked always failed and i am not real sure about the desired result,
    from some reading, it may always be best to start with a blank sheet, just delete the worksheet with previous pivot table then add a sheet to work with, that way you can make sure you are always working with the current data, a problem i had for a while when i changed the data range
    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

  16. #16

    Thread Starter
    Junior Member
    Join Date
    Feb 2018
    Posts
    16

    Re: Excel 2013. Creating a Pivot Table based on data in another worksheet using VBA.

    Hi Westconn,

    Using the tabular layout, I am slowly getting there. The details being shown make sense though I still have to reduce the number of values. The screen is currently displayed as attached which is a lot better than before.
    Attached Images Attached Images  

  17. #17
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,724

    Re: Excel 2013. Creating a Pivot Table based on data in another worksheet using VBA.

    if you want further help, manually create and post a workbook with a pivot table as desired

    i can try then, to create a duplicate by code

    or you can try recording macros to find what settings to use in your code
    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

  18. #18

    Thread Starter
    Junior Member
    Join Date
    Feb 2018
    Posts
    16

    Re: Excel 2013. Creating a Pivot Table based on data in another worksheet using VBA.

    Hi Westconn

    I will do that tomorrow. I am making very slow progress. But, at the end of this, we will know an awful lot more about VBA based pivot tables than we did a week ago :-)

  19. #19

    Thread Starter
    Junior Member
    Join Date
    Feb 2018
    Posts
    16

    Re: Excel 2013. Creating a Pivot Table based on data in another worksheet using VBA.

    Hi Westconn

    I have almost reached the end. I have done a fair bit more work and it is looking pretty good. I have attached the sample work book together with a "what I am getting" image and "what i am hoping to get" image.The full spreadsheet seems to work but the sample workbook is getting a "400" error on the line "PVTFieldXXXXX.Position = PVTFieldCountXXXXX" and I can not, for the life of me, see why.

    The original code that you put in using the Case statement worked for fields 1 to 6 but never reached fields 7 to 8. I found that using multiple Ifs, all fields were processed and that I was able to have more control over column headings etc.

    The two problems are currently:
    1. Hiding the Cancels column whilst displaying the Cancellations columns
    2. Summarising multiple records so only one line is displayed (the line being a summary of all records with the same store name and reason).

    I hope that this makes sense
    Attached Images Attached Images  
    Attached Files Attached Files

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width