troubleshooting Question

Excel macro refreshing multiple DCT

Avatar of dlan75
dlan75Flag for France asked on
Microsoft ExcelVBAMicrosoft Office
3 Comments1 Solution160 ViewsLast Modified:
Hi all,
I have an excel spreadsheet containing around 15 excel DCT.
On that sheet, there is a drop down menu allowing selecting a username, and when changing the value, a macro updates all the DTC present on the spreadsheet with the username and refreshes the data.
Problem is that now for any reason, it crashes excel except on high perf computer.
I don't know what to do

Here is the macro :

Dim pt As PivotTable
Dim strField As String

strField = "Cslts"

On Error Resume Next
'Application.EnableEvents = False
'Application.ScreenUpdating = False

    If Target.Address = Range("H1").Address Then
            For Each pt In ActiveSheet.PivotTables
                With pt.PageFields(strField)
                    For Each pi In .PivotItems
                        If pi.Value = Target.Value Then
                            .CurrentPage = Target.Value
                    Exit For
                            .CurrentPage = "(blank)"
                        End If
                    Next pi
                End With
            Next pt
    End If

End Sub


Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros