Wait for Source Document to Load before VBA runs

[Glasgowsmile]

Good morning,

I'm running into an issue with the following code. I am attempting to open a source document, add these formulas to that document and then extract the results of those formulas. The problem (I think) is that the source document is horrible optimized and runs really slowly so this VBA works before the cells have populated correctly in the source document which often leads to the retrieved data being 0. Is there a way I can prevent this code from loading until that document has finished loading in the background?

         Sheets("Cover").Range("D36").Formula = "=(AL5/Q36)*1"

         Sheets("Cover").Range("D38").Formula = "=SUM(AL5:AS5)"
         Sheets("Cover").Range("D39").Formula = "=(D38/Q36)*1"

         With Sheet8
            nextRow = .Range("B" & .Rows.Count).End(xlUp).Row + 1
            .Cells(nextRow, "B").Value = Sheets("Cover").Range("Q36").Value
            .Cells(nextRow, "C").Value = Sheets("Cover").Range("C38").Value
            .Cells(nextRow, "D").Value = Sheets("Cover").Range("C39").Value
            .Cells(nextRow, "E").Value = Sheets("Cover").Range("D36").Value
            .Cells(nextRow, "F").Value = Sheets("Cover").Range("D39").Value
            .Cells(nextRow, "H").Value = Sheets("Cover").Range("S4").Value
            .Cells(nextRow, "I").Value = Sheets("Cover").Range("S5").Value
         End With

 

[mumps]

Can you post your entire code including the lines that open the source document?

 

[Glasgowsmile]

Can you post your entire code including the lines that open the source document?

Here you go, the code that adds formulas to C38 and C39 works each time, no issues there - it's always D36, D38, and D39 that causes problems because the spreadsheet is filled with formulas that are generating that data when it's opened.

Sub Trend()
Application.DisplayAlerts = False
Dim crnt As Workbook
Dim source As Workbook
Dim nextRow As Long

With Application.FileDialog(msoFileDialogOpen)
      .Filters.Clear
      .Filters.Add "Files", "*.xlsx, *.xlsm"
      .AllowMultiSelect = False
      .Show
      If .SelectedItems.Count > 0 Then
         Workbooks.Open .SelectedItems(1)
         Set source = ActiveWorkbook
         

         Sheets("Cover").Range("C38").Formula = "=SUM(Data!G2:G2000)"
        
         Sheets("Cover").Range("C39").Formula = "=SUM(Data!M2:M2000)"
         
         Sheets("Cover").Range("D36").Formula = "=(AL5/Q36)*1"

         Sheets("Cover").Range("D38").Formula = "=SUM(AL5:AS5)"
         Sheets("Cover").Range("D39").Formula = "=(D38/Q36)*1"
         
         With Sheet8
            nextRow = .Range("B" & .Rows.Count).End(xlUp).Row + 1
            .Cells(nextRow, "B").Value = Sheets("Cover").Range("Q36").Value
            .Cells(nextRow, "C").Value = Sheets("Cover").Range("C38").Value
            .Cells(nextRow, "D").Value = Sheets("Cover").Range("C39").Value
            .Cells(nextRow, "E").Value = Sheets("Cover").Range("D36").Value
            .Cells(nextRow, "F").Value = Sheets("Cover").Range("D39").Value
            .Cells(nextRow, "H").Value = Sheets("Cover").Range("S4").Value
            .Cells(nextRow, "I").Value = Sheets("Cover").Range("S5").Value
         End With
         
        ''''''''''''''''''''''''''''''''''''''''''''

 

[mumps]

I've tidied up your code a bit. Try the code below. If there are a lot of formulae, the line in red turns off calculation to speed things up. The line in blue, turns calculation back on. I couldn't test it without access to your file.

Sub Trend()
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
    Dim crnt As Workbook, source As Workbook
    With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear
        .Filters.Add "Files", "*.xlsx, *.xlsm"
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then
            Workbooks.Open .SelectedItems(1)
            Set source = ActiveWorkbook           
            With Sheets("Cover")
               .Range("C38").Formula = "=SUM(Data!G2:G2000)"
               .Range("C39").Formula = "=SUM(Data!M2:M2000)"
               .Range("D36").Formula = "=(AL5/Q36)*1"
               .Range("D38").Formula = "=SUM(AL5:AS5)"
               .Range("D39").Formula = "=(D38/Q36)*1"
                Application.Calculation = xlCalculationAutomatic
               sheet8.Cells(sheet8.Rows.Count, "B").End(xlUp).Offset(1).Resize(, 7).Value _
                   = Array(.Range("Q36").Value, .Range("C38").Value, .Range("C39").Value, .Range("D36").Value.Range("D39").Value, .Range("S4").Value, .Range("S5").Value)
            End With
        End If
    End With
         
        ''''''''''''''''''''''''''''''''''''''''''''
End Sub

 

[Glasgowsmile]

I've tidied up your code a bit. Try the code below. If there are a lot of formulae, the line in red turns off calculation to speed things up. The line in blue, turns calculation back on. I couldn't test it without access to your file.

Sub Trend()
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
    Dim crnt As Workbook, source As Workbook
    With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear
        .Filters.Add "Files", "*.xlsx, *.xlsm"
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then
            Workbooks.Open .SelectedItems(1)
            Set source = ActiveWorkbook          
            With Sheets("Cover")
               .Range("C38").Formula = "=SUM(Data!G2:G2000)"
               .Range("C39").Formula = "=SUM(Data!M2:M2000)"
               .Range("D36").Formula = "=(AL5/Q36)*1"
               .Range("D38").Formula = "=SUM(AL5:AS5)"
               .Range("D39").Formula = "=(D38/Q36)*1"
                Application.Calculation = xlCalculationAutomatic
               sheet8.Cells(sheet8.Rows.Count, "B").End(xlUp).Offset(1).Resize(, 7).Value _
                   = Array(.Range("Q36").Value, .Range("C38").Value, .Range("C39").Value, .Range("D36").Value.Range("D39").Value, .Range("S4").Value, .Range("S5").Value)
            End With
        End If
    End With
        
        ''''''''''''''''''''''''''''''''''''''''''''
End Sub

This code gave me an error on the Array section so it won't run, but I'm less worried about that so I tried just adding the xlCalculationManual / Automatic but still coming up with empty results from these areas:

               .Range("D36").Formula = "=(AL5/Q36)*1"
               .Range("D38").Formula = "=SUM(AL5:AS5)"
               .Range("D39").Formula = "=(D38/Q36)*1"

 

[mumps]

In which sheet(s) are the ranges and cells (in red) located?

.Range("D36").Formula = "=(AL5/Q36)*1"
.Range("D38").Formula = "=SUM(AL5:AS5)"
.Range("D39").Formula = "=(D38/Q36)*1"

 

[Glasgowsmile]

In which sheet(s) are the ranges and cells (in red) located?

.Range("D36").Formula = "=(AL5/Q36)*1"
.Range("D38").Formula = "=SUM(AL5:AS5)"
.Range("D39").Formula = "=(D38/Q36)*1"

Sheets("Cover") - the problem, I believe, is that there is a macro in the background that runs when you open this source document and it adds all these formulas that need to run.

If we look at .Range("D36"), I need to divide the data in AL5 which has this formula "=COUNTIF($AG$5:$AG$3999,AL4)+COUNTIF($AG$5:$AG$3999,"<0")" and the AG range that it's referencing doesn't have any data when you initially open this document because there is VBA code that adds the formula to that range which gets the data.

Sheets("Cover").Range("Y5:AK4000").Formula = Sheets("Cover").Range("Y4:AK4").Formula

This is what I think the issue is... my VBA code is doing =(AL5/Q36)*1 before the data is even populated due to the formulas and so it copies 0. I thought an application.wait would fix this but it doesn't.

 

[mumps]

The three formulae in column D will refer to the active sheet. Is the "Cover" sheet the active sheet when the macro is run?

 

[Glasgowsmile]

The three formulae in column D will refer to the active sheet. Is the "Cover" sheet the active sheet when the macro is run?

It is but it takes a bit for it to load because of all the crap going on in the macros. I just had the thought... why pull the cover data when I can just reference the data that's already existing in the sheet and pull that instead.

 

[mumps]

Did you try that approach?