" .Showdetail = True " not running with F5 when cell on PowerPivot table selected

[e076841]

Hi,

When I run the macro by F8, it works perfect and the table, corresponsing to the selected cell of the pivot table, opens in a new sheet. However, when done with F5, it gives the runtime error '1004' and the macro terminates with a blank sheet open where cells(1,1) says " retirieving data for (the pivot table name) "

Welcome your thoughts

Regards

 

[e076841]

Maybe the below can help you understand better;
The code terminates after the line " wksPivot.Cells(1 + 11, 6).ShowDetail = True "

Regards

Dim i               As Long
Dim wksPivot        As Worksheet
Dim wksTemp         As Worksheet
Dim varIRR()        As Variant
 

Public Sub Pivot2()
    
    Dim lRow        As Long
    
    Set wksPivot = Worksheets("Pivot")
    
    With wksPivot
        lRow = .Range("f" & .Rows.Count).End(xlUp).Row - 1
    End With
        
    ReDim varIRR(1 To lRow - 11, 1 To 1)
    
    For i = 1 To UBound(varIRR, 1)
       Application.StatusBar = i & "/" & UBound(varIRR, 1) & " Completed"
        
        DoEvents
        wksPivot.Cells(1 + 11, 6).ShowDetail = True
        ShortBreak
 
   Next
    
    
 
End Sub



Sub ShortBreak()
    
    Dim ContinueTime As Date
    
    ContinueTime = Now + TimeValue("00:00:05")
    
    Do While Now() < ContinueTime
        DoEvents
    Loop
 

End Sub