Worksheets not calculating

CDelSignore

New Member
Joined
Mar 1, 2016
Messages
18
I'm using Excel 2010 and trying to one-button automate updating a spreadsheet. The button should refresh two tables from outside (networked) workbooks and then calculate worksheets in the open workbook, in a specified order.
Here is my macro the button is linked to:
Sub Workbook_UpdateAll()​
ActiveWorkbook.RefreshAll​
MsgBox "Done Refreshing Data and OEE Sheets"​
ActiveWorkbook.Worksheets("Calculation Sheet").Calculate​
MsgBox "Done Calculating Calc Sheet"​
ActiveWorkbook.Worksheets("Production Numbers").Calculate​
MsgBox "Done Calculating Production Numbers Sheet"​
ActiveWorkbook.Worksheets("Production").Calculate​
MsgBox "Done Calculating Production Sheet"​
End Sub

At this time, the two tables refresh as intended, but none of the worksheets calculate. The calculation sheet updates last lines of data on the Data and OEE tables, and lists out arrays that the Production Number and Production worksheets use as part of Indirect functions. The messageboxes come up, but the worksheets don't calculate. The order of calculation is important.

Any idea what's going wrong?
 
Last edited:

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

CDelSignore

New Member
Joined
Mar 1, 2016
Messages
18
Update: I recorded a Macro and got the following:
Sub UpdateAll()
' Refreshes linked tables and calculates sheets in proper order
ActiveWorkbook.RefreshAll
Sheets("Calculation Sheet").Select
ActiveSheet.Calculate
Sheets("Production Numbers").Select
ActiveSheet.Calculate
Sheets("Production").Select
ActiveSheet.Calculate
Sheets("TableOfContents").Select​
End Sub​

When I recorded the Macro, it performed exactly as I wanted it to. I copied the code and closed the file (without saving). When I reopened, I replaced the code (listed in the opening post) with the new code. It refreshed but did not calculate.

Can someone explain what's going wrong? I'd be close to pulling my hair out, but I'm bald and not touching the beard. :LOL::cool:

Update #2 ... if I run the Macro a second time, it calculates as desired.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,142
Messages
5,466,927
Members
406,510
Latest member
wizekor

This Week's Hot Topics

Top