Pivot Table Auto Referesh

[pauldaddyadams]

Hi,

I have a pivot table on the "customer" sheet which I need to automatically refresh if the user changes any of the data (this is restricted to columns C and F)

i have tried 3 different codes that i have found online but neither have worked (these are contained in the attached file)

does anyone have a code that refresh the table, rather than me having to right click the table and then hitting refresh?

I would only want it to auto refersh the pivot table on this "customer" sheet.

It is the blue graph I have the problem with

[pauldaddyadams]

I found my problem... I was including the code in a module rather than the sheet. However when I do this my sheet goes mental - i think its because of the auto filter - is there a way around this?

[pauldaddyadams]

I tried putting the pivot table on a new sheet "PivotTable" (the graph remained on the "customer" sheet) and used this macro below to update the pivot table based on if the customer sheet changes HOWEVER - this didnt work either!

Private Sub Worksheet_Calculate()

    'If data on this worksheet changes, refresh the pivot table
    Sheets("Customer").PivotTables("PivotTable1").RefreshTable

End Sub

Any ideas anyone?

[6StringJazzer]

Add this code to Worksheet_Change

   If Target.Column >= [B1].Column And Target.Column <= [I1].Column And Target.Row >= 14 Then
      PivotTables("PivotTable1").RefreshTable
   End If

[pauldaddyadams]

Hi, where should I place this code?

Does this assume my pivot tabel is on the "customer" sheet (as in the original example)?

[6StringJazzer]

Hi, where should I place this code?

Does this assume my pivot tabel is on the "customer" sheet (as in the original example)?

You already have a Sub called Worksheet_Change in the Customer sheet. I am talking about the file you attached above. Add my code to the beginning of that Sub.

[pauldaddyadams]

Hi,

Do you mean like this below? If so this didnt work as the pivot table didnt update unfortunatly. Any ideas?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCrit As Range

   If Target.Column >= [B1].Column And Target.Column <= [I1].Column And Target.Row >= 14 Then
      PivotTables("PivotTable1").RefreshTable
   End If

Set rngCrit = wksCrit.Range("CriteriaRng")
Application.EnableEvents = False

Select Case Target.Address
    Case Range("SelPro").Address
        rngCrit.Cells(2, 1).Value = Target.Value
    Case Range("SelGeo").Address
        rngCrit.Cells(2, 2).Value = Target.Value
    Case Range("SelSpacer").Address
        rngCrit.Cells(2, 3).Value = Target.Value
    Case Range("SelText").Address
        rngCrit.Cells(2, 4).Value = Target.Value
    Case Range("SelGlass").Address
        rngCrit.Cells(2, 5).Value = Target.Value
End Select

If Range("SelPro").Value = "" Then
    rngCrit.Cells(2, 1).ClearContents
End If
If Range("SelGeo").Value = "" Then
    rngCrit.Cells(2, 2).ClearContents
End If
If Range("SelSpacer").Value = "" Then
    rngCrit.Cells(2, 3).ClearContents
End If
If Range("SelText").Value = "" Then
    rngCrit.Cells(2, 4).ClearContents
End If
If Range("SelGlass").Value = "" Then
    rngCrit.Cells(2, 5).ClearContents
End If

If Not rngCrit Is Nothing Then
  wksMovies.Range("GlassList").AdvancedFilter _
    Action:=xlFilterCopy, _
      CriteriaRange:=rngCrit, _
      CopyToRange:=Range("ExtractData"), Unique:=False
End If

exitHandler:
    Application.EnableEvents = True
    Exit Sub
errHandler:
    Resume exitHandler

End Sub

[6StringJazzer]

That's exactly right. I used it in your original attachment, and it definitely updates the pivot table, but I can't figure out what the rest of your code is supposed to do or why. So maybe my new code needs to go at the end of the sub instead of the beginning.

I note that when I try to update something like Volume, the pivot data regenerates but then the number is reverted back to what it was before I changed it, so I just don't know how your worksheet is supposed to work.

[pauldaddyadams]

Hi,

Thanks for looking into this.

I have tried your spreadsheet and I still can not see the pivot table graph refreshing automatically, am I missing something?

Can I check that you are looking at the blue graph? The red graph updates but the blue graph doesnt.

Regardging the model, ill attempt to explain it. The user has the ability on the customer sheet to select either of the following:
Product Group
Georgian
Spacer
Textured
Glass

Based on the if there is an entry in the corrosponding cells cells B14:I162 will automatically filter (this bit works)

If, for instance, you select the following:
Product Group: EnergiKare

Then the pivot table should only show Energikare products and the type of spacer used. The pivot table will do this but only once you have right clicked and selected refresh (its this bit I would like to automate it).

I hope that explains it as this part is vital to my model.

[6StringJazzer]

Let's revisit what you asked for. I think I see the problem.

I have a pivot table on the "customer" sheet which I need to automatically refresh if the user changes any of the data (this is restricted to columns C and F)

No, that is not want you need. You don't want to refresh if the user changes any of the data. You need to automatically refresh if the user changes the data in cells C3, C5, C7, C9, C11, or F4. You did not explain how someone is going to use this worksheet until your last post. When there are many rows of data the natural assumption (in the absence of a description) is that the user is going to update data in the rows, or add or delete rows. But it looks like that is not what you intend.

Attached is a solution that fits that description.

[pauldaddyadams]

Hi,

For ages I was thinking that this wasnt working and I know why...

The pivot table does work and refresh when data is changed (A MASSIVE THANK YOU) however, it doesnt update when cells C3, C5, C7, C9, C11, or F4 are deleted/cleared. The autofilter updates but is there anyway in which your code can be amended slighty so if its the user clears one of the cells then it also updates the pivot table?

(ps: sorry for my ambiguity previously)

[6StringJazzer]

These is because my code to update the pivot table is done first in the Sub. If you move it to the end, just before the exitHandler label, it will work.

[pauldaddyadams]

Worked!!

Thank you for all your time on this!!