Wait for Source Document to Load before VBA runs

G

Glasgowsmile

Active Member
Joined
Apr 14, 2018
Messages
280
Office Version
  1. 365
Platform
  1. Windows
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?

VBA Code: 
         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
 
Sort by date Sort by votes
Can you post your entire code including the lines that open the source document?
 
Upvote 0
mumps said:
Can you post your entire code including the lines that open the source document?
Click to expand...
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.

VBA Code: 
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
         
        ''''''''''''''''''''''''''''''''''''''''''''
 
Upvote 0
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.
Rich (BB code): 
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
 
Upvote 0
mumps said:
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.
Rich (BB code): 
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
Click to expand...
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:

VBA Code: 
               .Range("D36").Formula = "=(AL5/Q36)*1"
               .Range("D38").Formula = "=SUM(AL5:AS5)"
               .Range("D39").Formula = "=(D38/Q36)*1"
 
Upvote 0
In which sheet(s) are the ranges and cells (in red) located?
Rich (BB code): 
.Range("D36").Formula = "=(AL5/Q36)*1"
.Range("D38").Formula = "=SUM(AL5:AS5)"
.Range("D39").Formula = "=(D38/Q36)*1"
 
Upvote 0
mumps said:
In which sheet(s) are the ranges and cells (in red) located?
Rich (BB code): 
.Range("D36").Formula = "=(AL5/Q36)*1"
.Range("D38").Formula = "=SUM(AL5:AS5)"
.Range("D39").Formula = "=(D38/Q36)*1"
Click to expand...

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.
VBA Code: 
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.
 
Upvote 0
The three formulae in column D will refer to the active sheet. Is the "Cover" sheet the active sheet when the macro is run?
 
Upvote 0
mumps said:
The three formulae in column D will refer to the active sheet. Is the "Cover" sheet the active sheet when the macro is run?
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
VBA - Checking for Duplicates
Replies
2
Views
1K
cwhaley1
C
M
  • Question Question
inputting formulas across multiple sheets with varying number of rows
Replies
3
Views
205
mrider123
M
I
  • Question Question
Unable to run VBA code
Replies
6
Views
319
RoryA
RoryA
M
  • Question Question
Power Query with Dynamic Data Source
Replies
4
Views
2K
Alex Blakenburg
A
R
  • Question Question
VBA- Adding source and destination cells together rather than overwriting
Replies
4
Views
305
Alex Blakenburg
A

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top