Automatic Refresh causing paste issues

[SDP]

Hi

I am using code that automatically refreshes data for my pivot table. My issue is that I'm unable to paste anything on this worksheet, because it refreshes constantly. For example, I have a range B4:E5 that I would like to copy and paste in range B50:E51. I can copy this but am unable to paste it.


Is there a way(perhaps using a if statement) that I could use to get around this issue?

Code I'm using for my refresh
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
PivotTables("PTWeeks").RefreshTable


End Sub



Thank you very much!

[p45cal]

Refreshing the pivot table on selection_change is a tad excessive. You can restrict the updating of the pivot table to when the selection includes any cell within the pivot table:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set ssss = PivotTables("PTWeeks").TableRange2
If Not Intersect(ssss, Target) Is Nothing Then PivotTables("PTWeeks").RefreshTable
End Sub

or to any other range you want.
But what are you trying to achieve by refreshing so much?
You could use other event handlers to refresh the pivot table when it's source data changes. Is the source data on the same sheet?
Perhaps attach a simple file.

[Paul_Hossler]

Some assumptions since there was no workbook attached (hint, hint)

1. Not _SelectionChange, use _Change

2. You need to find the PT's source data, and see if any of those cells changed

3. Turn off EnableEvents while the PT refreshes, then turn them back on

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ptCacheIndex As Long
    Dim ptSourceData As String
    Dim ptSourceRange As Range
    Dim n As Long
    
    ptCacheIndex = PivotTables("PTWeeks").CacheIndex
    ptSourceData = ActiveWorkbook.PivotCaches(ptCacheIndex).SourceData
    n = InStrRev(ptSourceData, "!")
    ptSourceData = Right(ptSourceData, Len(ptSourceData) - n)
    ptSourceData = Application.ConvertFormula(Formula:=ptSourceData, fromReferenceStyle:=xlR1C1, toReferenceStyle:=xlA1)
    Set ptSourceRange = Range(ptSourceData)
    
    If Intersect(Target, ptSourceRange) Is Nothing Then Exit Sub

    Application.EnableEvents = False
    PivotTables("PTWeeks").RefreshTable
    Application.EnableEvents = True

End Sub

This could probably be simplified a little

[SDP]

Hi Thanks for your help. I really appreciate it. The data is all on one worksheet. The reason for refreshing so much is 1) Because that's about as far as my skill level goes:-) 2) The users of the file have a very basic knowledge using excel. There is a risk that they won't remember(or even know how) to refresh the pivot table. The data I'm trying to copy and paste is the source data of the pivot table. The suggestion you gave took care of the copy and paste issue, but unfortunately the pivot table isn't refreshing automatically.

[SDP]

Hi Paul

Thank you very much for your help on this. The code is taking care of the copy and paste issue, but unfortunately the pivot table isn't refreshing automatically. I've added the file. The Pivot table can be found at cell AU135

[Paul_Hossler]

The PT data source from your attachment was set to '[Planning Linesteam Final.xlsm]Jan 2017'!$AL$2:$AQ$155

I changed it to the AL:AQ table in your attachment ('Jan 2017'!$AL$2:$AQ$155)

I added my WS_Change macro


Changing values in the AL:AQ table (the PT's data source) refreshes the PT and the PT chart

[SDP]

Thanks Paul! You are a legend!