Hello;
I am writing a macro in which I need to refresh the data on a specific sheet to get the up-to-date data just generated by EMS (works fine) with the first RefreshAll statement, the following commands are executed but the macro fails at the last line in red because the second RefreshAll is not executed or too slow to be done before the rest of the code is executed (there are other code after the line in red and it is working). I have tried 'Workbooks(ThisWorkbook.Name).RefreshAll and ActiveWorkbook.RefreshAll as well as trying to make it wait for up to 30 seconds but nothing works.
However when I use the debugger and go through it all with F8 everything works fine. Anyone has any idea on how to fix this? Is there a way to make the rest of the macro wait for the RefreshAll execution and why would the first RefreshAll work and not the second ?
Thank you for your time
>>> use code - tags <<<
I am writing a macro in which I need to refresh the data on a specific sheet to get the up-to-date data just generated by EMS (works fine) with the first RefreshAll statement, the following commands are executed but the macro fails at the last line in red because the second RefreshAll is not executed or too slow to be done before the rest of the code is executed (there are other code after the line in red and it is working). I have tried 'Workbooks(ThisWorkbook.Name).RefreshAll and ActiveWorkbook.RefreshAll as well as trying to make it wait for up to 30 seconds but nothing works.
However when I use the debugger and go through it all with F8 everything works fine. Anyone has any idea on how to fix this? Is there a way to make the rest of the macro wait for the RefreshAll execution and why would the first RefreshAll work and not the second ?
Thank you for your time
>>> use code - tags <<<
Code:
'Refresh the Data worksheet and execute the filter command on the filter worksheet
Workbooks(ThisWorkbook.Name).RefreshAll
Call Sheets("FILTER").btnFiltre_Click
Sheets("Summary").Select
'Refresh the Summary worksheet to update it with the new data from the FILTER sheet
'Workbooks(ThisWorkbook.Name).RefreshAll
ActiveWorkbook.RefreshAll
'trying to slowdown execution because the previous RefreshAll is skipped in normal execution but works fine with F8 in the debuger
'Application.Wait (Now + TimeValue("00:00:05"))
'Separating the All invoices.pdf file into separate pages
Splitpdf
'Deleting all existing BOUCI worksheets
For Each ws In ThisWorkbook.Worksheets
If ws.Name Like "*BOUCI*" Then
ws.Delete
End If
Next
'Deleting copy of "Clients Database" sheet to make sure we copy the latest version everytime
ThisWorkbook.Sheets("Clients Database").Delete
'Creating every BOUCI tab in the workbook, changing names of pdf to match the invoice number used and creating the excel backup file.
For Each BOUCI In Range("A:A")
If BOUCI.Value Like "BOUCI*" Then
gdate = Format(Range("B" & a).Value, "yyyy-mm-dd - ")
Range("J" & a).Select
Selection.ShowDetail = True
Sheets(a).Name = gdate & BOUCI.Value
ActiveSheet.Copy
ActiveWorkbook.SaveAs filename:=sPath & "\" & gdate & BOUCI.Value & ".xlsx"
Application.ActiveWorkbook.Close False
Name sPath & "\" & gdate & "All Invoices " & gNum & ".pdf" As sPath & "\" & Sheets(a).Name & ".pdf"
Last edited by a moderator: