PDA

View Full Version : Automatic Refresh causing paste issues



SDP
01-17-2018, 01:36 PM
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
01-17-2018, 02:08 PM
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
01-17-2018, 02:20 PM
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
01-17-2018, 04:00 PM
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
01-17-2018, 04:20 PM
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
01-17-2018, 05:39 PM
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
01-17-2018, 06:27 PM
Thanks Paul! You are a legend!