Excel pivot table query - retaining filters on pivot in absence of data

[Markylex]

Hello all,

I have a pivot table / potential Excel VBA related query, which I am hoping someone might be able to assist with?

Within the Excel VBA project I am currently working on, I have a pivot table that is filtered to look at invoices in my raw data, that have a 'withheld' or 'allocated' status. As this raw data comes from different suppliers, there may be times where it doesn't contain either 'withheld' or 'allocated' invoices, however I still want the pivot table to retain the same filters and display nothing in such instances. Whilst I have recently become familiar with the pivot table option 'show items with no data', which would ordinarily solve this issue, this currently does not work for me. This is due to a part in the macro I have created, which updates the source data of all the pivots in my workbook, based on the new set of raw data provided.

At present, I can think of two ways to fix my issue:

• Adjust the source data on my pivots to be 'static' and look at select columns in the raw data, rather than updating them via the macro to match the exact number of rows in the raw data. I am unsure if this goes against 'best practice' though, as the pivot will be picking up a lot of empty cells, which will obviously filter through to the pivot table as 'blank'.
• Add ficticious data into the raw data, to ensure there's always a row that has the 'withheld' and 'allocated' status, however assign nil values to these. Once the macro runs, including updating the source data for the pivots, I could then have these further steps as part of the macro:
  • Apply the 'show items with no data' option to the relevant pivot.
  • Delete out the fake data from the raw data.
  • Refresh the pivot.
  • This way, the only issue would be the small number of blank lines in the absence of the fake data, which I guess could also be filtered out of the pivot? I assume this beats the pivot searching through whole columns though?

I wondered what people's opinions were on the above two points, or if anyone had any alternative suggestions to remedy my situation? For a bit of added context, this project is for the reconciliation of supplier statements and is for a different team in my company to my own. As such, I am trying to avoid any presentation issues with any of the pivots and also preventing the need for people using the file to manually mess around with the pivots/data.

I hope this makes sense, however should anyone have any questions/require further information, then let me know and I am happy to provide.

Thanks in advance for any advice provided!

 

[Markylex]

Hello all,

I was trying to avoid bumping this, however it seems I am not getting any feedback and I am trying to get this project completed over this weekend. That being said, does anyone have any advice on the above?

Kind regards,
Mark.

 

[Markylex]

Hello all,

Sorry to bump again but any thoughts?

Kind regards,
Mark.

 

[Markylex]

Hello all,

Just thought I'd quickly update this thread to add a couple screenshots and code that might make things clearer.

Firstly, this is the macro code used to update all the pivots in my workbook, including the pivot I have my query with:

Sub UpdatePivots()

    'Declare variables to be used throughout sub
 
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Supplier Data")
 
    Dim ws2 As Worksheet
    Set ws2 = ThisWorkbook.Sheets("Voyage Data Rec")
 
    Dim ws3 As Worksheet
    Set ws3 = ThisWorkbook.Sheets("Pivots")

    Dim ws4 As Worksheet
    Set ws4 = ThisWorkbook.Sheets("Pivots 2")
 
    Dim source_data As Range
    Dim pt As PivotTable
    Dim lastrow As Long
    Dim lastcol As Long
 
    'Update data source for 'Pivots' sheet, based off 'Supplier Data' tab
 
    lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    lastcol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
 
    Set source_data = ws.Range(ws.Cells(1, 1), ws.Cells(lastrow, lastcol))
 
    For Each pt In ws3.PivotTables
        pt.ChangePivotCache ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=source_data)
    Next pt
 
    'Update data source for 'Pivots 2' sheet, based off 'Voyage Data Rec' tab
 
    lastrow = ws2.Cells(Rows.Count, 1).End(xlUp).Row
    lastcol = ws2.Cells(1, Columns.Count).End(xlToLeft).Column
 
    Set source_data = ws2.Range(ws2.Cells(1, 1), ws2.Cells(lastrow, lastcol))
 
    For Each pt In ws4.PivotTables
        pt.ChangePivotCache ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=source_data)
    Next pt
End Sub

• I am currently losing the 'show items with no data' option from the pivot table in question, which I assume is due to the pivot cache being amended?

Secondly, this is the pivot that I'd like to 'show items with no data' in, when the filtered items (withheld or allocated) no longer appear in my raw data:

• Based on my raw data, this pivot consists of column F for the filter, column D for the rows and columns C, E and K for the values.

Lastly, this is a snippet to demonstrate what the raw data looks like that is feeding into the pivot tables, the above pivot included:

This raw data frequently changes in size due to different suppliers, hence why there's a macro to amend the pivot sources to match changes in the raw data.

Hope this helps everyone!

 

[Alex Blakenburg]

Have you considered converting the data into an Excel Table so that the datasource automatically updates ?

 

[Markylex]

Thanks for your suggestion @Alex Blakenburg. It is something that has crossed my mind over the past couple of days following some research. With that said, admittedly, I haven't had much exposure to standard Excel Tables, so wanted to hold fire on this idea until getting some feedback from others first. That in mind, I'm happy to look into this as a solution to the above and learn what's required.

If you don't mind, is there any chance you could clear up a couple queries I have off the top of my head:

• I assume the table would always exist in the document (just like the pivots), rather than being created as part of a macro each time? If so, each time I add a new set of 'raw data' to the table, I'd want all the old data deleted out, as it would at that point be irrelevant when relating to a different supplier to the one that's the current focus. Would the deletion of the old data have to follow the new data being added, so the table actually remains?
• Columns D to K in the above screenshot are actually formula driven, mainly from lookups based on columns A to C against other raw data sheets. Currently, a macro finds the last row of the above data sheet, based off those first 3 columns and pulls the formulas down accordingly. How would these formulated fields work as part of the table?

Hope this makes sense and thanks in advance for any further support!

 

[Alex Blakenburg]

1) Refreshing the data
There are couple of ways of going about this.
• Delete the databodyrange then copy in the new data.
• Clearcontent on Columns A to C, copy in the new data, then check if the new data is shorter than the previous and delete the additional rows
(it will automatically expand if shorter) - if you have a lot of data this may be faster performance wise.

2) Formulas
one of the advantages of tables is that if you have applied formulas consistently to all rows in the table, the formulas will autopopulate to whatever rows are in the table

 

[Markylex]

Thanks for the above clarification @Alex Blakenburg. I've started working on using an Excel Table for the data and whilst I feel like my project is now making solid progress, today has brought on a couple more questions.

If it's okay with you, could you advise on the following:

• I've now set the file up so that the table initially contains only one row, where columns A to C have already had contents cleared and columns D to K contain the relevant formulas. Does this seem like a reasonable setup to you, so that each time there's a new set of raw data, it simply gets copied into A to C and the formulas then autopopulate? This should also ensure the data is never shorter than the previous.
• If I were to include the inputting of the data into the table as part of a macro, how would I reference the table when copying the data over? For context, I've tested declaring a ListObject variable and setting this to be my table, alongside creating a 'source_data' variable, which is the range of the raw data, calculated through the last row & column in said data. Whilst these points are both fine, I am then stuck on how to actually copy the contents of the source data variable into the first cell of the table.

Really appreciate the continued support.

 

[Alex Blakenburg]

Technically you can remove all the rows and the formulas are still remembered by the table and will reappear when you start adding data.
See if the below code helps.
PS: If you find it is too slow, we can looks at clearing columns A-C and then resizing if required to see if that is faster.

Sub chgListObject()

    Dim lo As ListObject
    Dim newrow As ListRow
    
    Set lo = Range("Table1").ListObject
    
    ' Clear the table
    lo.DataBodyRange.Rows.Delete
    ' Add a new row and store the reference to it
    Set newrow = lo.ListRows.Add
    ' Resize the target row and assign the values to be copied in
    ' This is the equivalent to copy values
    newrow.Range.Resize(, 3).Value = Range("I10:K10").Value
    
    ' Use copy paste
    Set newrow = lo.ListRows.Add
    Range("I11:K11").Copy
        newrow.Range.Cells(1, 1).PasteSpecial Paste:=xlPasteValues

End Sub

 

[Markylex]

Apologies for the delay in responding, it's been a really busy few days for me!

Thanks for taking the time to create the above code. I took a couple of bits from it and combined it with what I'd already created, leading to the following:

Sub UpdateSupplierRec()

    'Declare variables to be used throughout code
   
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Supplier data")
   
    Dim ws2 As Worksheet
    Set ws2 = ThisWorkbook.Sheets("Supplier data rec")
 
    Dim tblsupplier As ListObject
    Set tblsupplier = ws2.ListObjects("Table1")
   
    Dim lastrow As Long
    Dim lastcol As Long
    Dim source_data As Range
    Dim newrow As ListRow
  
    'Set source data variable
   
    lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    lastcol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
   
    Set source_data = ws.Range(ws.Cells(2, 1), ws.Cells(lastrow, lastcol))
   
    'Copy source data variable into table
   
    Set newrow = tblsupplier.ListRows.Add
    source_data.Copy
    newrow.Range.Cells(1, 1).PasteSpecial Paste:=xlPasteValues

End Sub

This seems to do the trick where I'm now starting with no data rows in my table, only the header row. Also, everything seems to run reasonably fast so far. What are your thoughts on this - is there anything you'd recommend I change?

On another note, I did play around with your code in full, including in a fresh workbook I setup, with it written exactly as above. I couldn't quite seem to get a result I would expect and so it's left me a bit confused as to where I was going wrong. Just to double check my understanding, is there any chance you could explain the purpose of this line in a little more detail, including the chosen range:

newrow.Range.Resize(, 3).Value = Range("I10:K10").Value