I have an Excel file with a tab that is linked to an Access table. Other tabs in the workbook have named formulas that refer to the data in the data range from the linked tab. The formulas are SUMIFS.
I have the workbook and all the sheets unprotected. I click on Data>Refresh All. Then at the bottom of the screen I see the following messages:
-Connecting to Datasource…
-Preparing worksheet…
-Calculating
The entire workbook calculates and the formulas return the correct values.
When I create a macro button to execute the Refresh All and I click the button, I see the file connecting to Datasource – Preparing worksheet – calculating, however the formulas don’t return a value. If I click in a cell containing the named formula, then press enter, the cell recalcs and displays the correct value.
Here is the code on the button.
Sub Button1_Click()
ActiveWorkbook.RefreshAll
End Sub
I don’t know what I’m missing. Why would a manual Refresh All work, but not through a macro?
I have the workbook and all the sheets unprotected. I click on Data>Refresh All. Then at the bottom of the screen I see the following messages:
-Connecting to Datasource…
-Preparing worksheet…
-Calculating
The entire workbook calculates and the formulas return the correct values.
When I create a macro button to execute the Refresh All and I click the button, I see the file connecting to Datasource – Preparing worksheet – calculating, however the formulas don’t return a value. If I click in a cell containing the named formula, then press enter, the cell recalcs and displays the correct value.
Here is the code on the button.
Sub Button1_Click()
ActiveWorkbook.RefreshAll
End Sub
I don’t know what I’m missing. Why would a manual Refresh All work, but not through a macro?