Can a macro pause while a PT refreshes, then continue?

[FormatCReturn]

Good afternoon, all

I am working with some large pivot tables, generated by PowerPivot, and I'm trying to write a macro to automate some of the manual process, so I can turn it on, let it run, and come back to it later. Here's what I'm doing - I select a company name from a dropdown list, PP populates all the data fields (takes 1 or 2 minutes usually) then I copy/paste everything as values and formats into a new sheet. My macro currently selects the next entry in a long list of company names, plugs it into the dropdown field and copies to a new sheet. The problem is that PP can't populate the data fields fast enough and I end up with a blank document copied to the new sheet. I tried the Application.Wait function and it didn't work - just paused the whole operation for 2 minutes and did the same thing. Does anybody know if VBA is able to pause an excel macro while a pivot table to refreshes, then proceed with the rest of the code after a certain amount of time elapses? My current code is below.
Thanks!

Sub Profile_Generator()
'
' Profile_Generator Macro
'
' Keyboard Shortcut: Ctrl+l
'
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.Copy
    Windows("Multi Site Profile Generator 2.0 - 03182013.xlsm").Activate
    Range("K5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


   'this next piece of code tells the macro to wait 2 min before continuing


    Application.Wait (Now() + TimeValue("00:02:00"))
    
        
        '***THE NEXT SECTION IS THE COPY/PASTE CODE ATTACHED TO THE COPY BUTTON ON THE PROFILE GENERATOR FILE***
        
        Dim fromWorkbook As Excel.Workbook
    Dim toWorkbook As Excel.Workbook


    Dim fromWorksheet As Excel.Worksheet
    Dim toWorksheet As Excel.Worksheet


    Dim fromRange As Excel.Range
    Dim toRange As Excel.Range
    
    Dim fromSolutionRange As Excel.Range
    Dim fromRevenueRange As Excel.Range
    Dim fromRevImpactRange As Excel.Range
    
    Dim row As Integer
    Dim col As Integer
    
    Dim solutionCol As Integer
    Dim revenueCol As Integer
    Dim revImpactCol As Integer
    
    Dim HQRow As Integer
    Dim RemoteRows_start As Integer
    Dim RemoteRows_end   As Integer
    
    ' ActiveWorkbook is the one with the current focus; ThisWorkbook is the one running this code.
    ' Know the difference.
    '
    ' See 10 ways to reference Excel workbooks and sheets using VBA | TechRepublic
    Set fromWorkbook = ActiveWorkbook
    Set fromWorksheet = fromWorkbook.ActiveSheet
    
    Set fromSolutionRange = Range("Solution_Column")
    Set fromRevenueRange = Range("Revenue_Column")
    Set fromRevImpactRange = Range("Rev_Impact_Column")
    
    solutionCol = fromSolutionRange.Column
    revenueCol = fromRevenueRange.Column
    revImpactCol = fromRevImpactRange.Column
    
    HQRow = Range("HQ_Row").Cells(1, 1).row
    RemoteRows_start = Range("RemoteSite_Rows").Cells(1, 1).row
    RemoteRows_end = Range("RemoteSite_Rows")(Range("RemoteSite_Rows").Count).row
    
    Set fromRange = Range("CopyRange")
    fromRange.Select
    Selection.Copy
    Workbooks.Add
    
    ' And now the newly added workbook is the ActiveWorkbook (selected and focused).
    ' We will be switching back and forth.
    Set toWorkbook = ActiveWorkbook
    Set toWorksheet = toWorkbook.ActiveSheet
    
    ' Name the workbook and worksheet to be the same as the currently selected LEGULTNUMALL
    'toWorksheet.Name = Range("Selected_LEGULTNUMALL").Value
    'toWorkbook.Set = Range("Selected_LEGULTNUMALL").Value
      
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False


    Set toRange = toWorksheet.Range(fromRange.Address)
    
    Application.ScreenUpdating = False
    
    For col = 1 To fromRange.Columns.Count
       toRange.Columns(col).ColumnWidth = fromRange.Columns(col).ColumnWidth
    Next
    
    ' This might be a good time to copy the 'Solution Lookup' worksheet to our new workbook
    ' since we will be referencing it in the next step.
    fromWorkbook.Sheets("Solution Lookup").Copy Before:=toWorkbook.Sheets(2)


    For row = 1 To fromRange.Rows.Count
       toRange.Rows(row).RowHeight = fromRange.Rows(row).RowHeight
       'If row = 22 Or (row >= 28 And row <= 526) Then
        If row = HQRow Or (row >= RemoteRows_start And row <= RemoteRows_end) Then


          ' Formula for Revenue Lift
          toRange.Cells(row, revenueCol).Formula = "=IFERROR(VLOOKUP(CS" & row & ", 'Solution Lookup'!$A$1:$B$10,2,FALSE), """")"
          
          ' Formula for Revenue Impact must consider case where site is not a customer, hence the iferror
          'IF(BY22="", "", IFERROR(BY22-AO22, BY22))
          toRange.Cells(row, revImpactCol).Formula = "=IF(CU" & row & " = """", """", IFERROR(CU" & row & "-AO" & row & ", CU" & row & "))"
          
          ' Set "Data Validation" property for Solution drop down list
          toWorksheet.Activate
          toRange.Cells(row, solutionCol).Select
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="='Solution Lookup'!$A$1:$A$10"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
       End If
    Next


    ' Now that we have resized all the ColumnWidths and RowHeights, we are ready to copy the "BW" Column
    ' which contains our lovely triangles (arrows).
    
    ' NOTE:  THE COLUMN WITH THE "LOVELY TRIANGLES" HAS BEEN DELETED.  REMOVED THIS SECTION OF CODE FROM THE MACRO DURING DEBUGGING. -LL
    
    Application.ScreenUpdating = True


End Sub

 

[FormatCReturn]

Also tried the Threading.Thread.Sleep method, but couldn't get it to run.

 

[jasmith4]

Set the PivotTable object's PivotCache.BackgroundQuery property to False.

 

[FormatCReturn]

Thanks for the reply, jasmith. I attempted to do this, but it didn't work. Debugger said "unable to get the PivotTable proporties for the worksheet class". Is there another way to do this besides encoding the macro? This is a Powerpivot table written by an outside contractor. VB was unable to retrieve the pivottable properties for the worksheet, presumably because it's a PP table. Anybody have other ideas?
Thanks again.

 

[jasmith4]

I've never heard of PowerPivot, but isn't there some Background property you can turn off?

Also, check Tools / References and see if there's something on that long list that comes from PowerPivot.

 

[FormatCReturn]

Powerpivot is an add-on for Excel 2010 and up. Basically, it's pivot table on steroids. Pretty sik. It's good for cubeset data, allows millions of rows of data, can generate really snazzy looking reports, and runs much faster than a typical pivot table. Unfortunately, I'm not familiar enough with it from a coding standpoint to know what on earth it's doing with this pivot table I'm working with. It doesn't show in the worksheet's code, for example, and the controls on the add in tab don't have what I need. Ah well. I'm at that point where I have to decide if putting more time into my macro is actually less cost effective that just doing the darn thing manually. you win some, you lose some.
Thanks again for your help.

 

[RonBarrett]

I have also run into the same issue of wanting to pause a macro's execution while allowing CUBE formulas #GETTING_DATA from a PowerPivot data model. My objective is to populate scorecards then save them as PDF files.

Various techniques I've tried have been unsuccessful because they pause the gathering of data along with pausing the macro. The techniques I've tried include:

• Application.wait for a specific number of seconds
• For…Next as an incremental counter
• Do While…Loop to check on the value of an update flag cell

Does anyone have an alternative idea?

 

[ZigZigZig]

I have also run into the same issue of wanting to pause a macro's execution while allowing CUBE formulas #GETTING_DATA from a PowerPivot data model. My objective is to populate scorecards then save them as PDF files.

Various techniques I've tried have been unsuccessful because they pause the gathering of data along with pausing the macro. The techniques I've tried include:

• Application.wait for a specific number of seconds
• For…Next as an incremental counter
• Do While…Loop to check on the value of an update flag cell

Does anyone have an alternative idea?

Real late to the party but try this

Application.CalculateUntilAsyncQueriesDone

 

[cogswel__cogs]

I often code in vba using pivottables.
Do iI need to worry about a particular pivottable not upadting before macro runs or is this more an issue because person is using power pivot.
I don't think I have ever had this problem, but then again never knew it might have been happening.