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
Re: Excel 2013. Creating a Pivot Table based on data in another worksheet using VBA.
can you post a sample workbook? zip first
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.
1 Attachment(s)
Re: Excel 2013. Creating a Pivot Table based on data in another worksheet using VBA.
Quote:
Originally Posted by
westconn1
can you post a sample workbook? zip first
Zip file attached
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
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.
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
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
Re: Excel 2013. Creating a Pivot Table based on data in another worksheet using VBA.
i commented out 2 lines and put replacements below
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.
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
Quote:
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
1 Attachment(s)
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
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
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.
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
1 Attachment(s)
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.
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
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 :-)
2 Attachment(s)
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