Excel/Run macro consecutively
Thanks for your prompt respond on my last question, the answer you provided was
For i = 1 To 3
I used it in my workbook as below:
Dim WS_Count As Integer
WS_Count = ActiveWorkbook.Worksheets.Count
For i = 3 To WS_Count
And calculating in my workbook means sendkeys #Alt+Ctrl+s# which is the hot keys of my reporting tools.
The issue now is: excel calculated one sheet only from my whole workbook then it's stoped. without calculating the rest of sheets.
Is there a way to keep excel calculating all sheets within my workbook one by one ? #because if I run it for all sheets together, excel will hang#.
In another word, is there a way fo force excel execute #Alt + Ctrl + s# for each sheet separately #but do it for all sheet at the end#.
Thanks in Advance
Hi again Mahmoud,
Yes, adding the SendKeys does not help because it only calculates the currently active worksheet, not worksheet(i), and it is always preferable to use the VBA method rather than SendKeys. Your other modifications to the code should be just fine.
When I tested it with a simple set of worksheets it worked fine. The only reason I can think of for it not working for you would be if the sheets must be calculated in a particular order (that is different from the tab order of the sheets, which is what my code does) in order to work properly. If this is the case then let me know the necessary order and I'll rewrite the code to force the correct order.
Also, there is a reason why it hangs when you do the calculations all at once, and this indicates that the calculation order IS necessary to having the sheets calculate properly.
PS. I just thought of another reason it may not be working. If your worksheets retrieve data from a web data feed (you mentioned "reporting tools") that returns control to the calling program before returning its data this would definitely cause such a problem. The only solution I can think of for this possible problem would be to introduce a wait statement in the code to provide time for the results to be returned, or to loop in the code until the desired data becomes available.
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here