Set to "Auto Calculate" via VBA.

[hutch@edge.net]

Greetings again, Gurus.

As usual, I'm perplexed. I have given some of my co-workers a workbook containing a macro that formats data they receive from multiple sources into a standardized format. The macro include formulas that remove spaces from part numbers, replaces "No" with "N", and a bunch of other things. The workbook has been working great for several months now.

Yesterday, one of them sent me the workbook, containing data they had pasted in from multople sources, and said "Only the first part number is being copied all the way down the sheet". I determined it was because somehow the formula calculation had been set to "Manual", and since I paste values after the formulas do their job, this was causing the first part number to be copied all the way down the range. (My co-worker swears he never changed the calculation to "Manual".)

I think the easiest fix would be to insure the workbook is set to automatically recalculate formulas before the macro begins running. The next option would be to force workbook to recalculate everytime a new formula is entered, (about 30 times, in all).

Is there a way to set the workbook to automatically calculate via VBA, and if so, how? If not, do any of you have any ideas about how to best overcome this problem?

Thanks in advance for any help you can offer.

[DonkeyOte]

for info. on controlling calculation from VBA see: http://www.decisionmodels.com/calcsecretsh.htm

[hutch@edge.net]

Beautiful, DonkeyOte. All I had to do was add the following code to the "This Workbook" section in VBA:

Private Sub Workbook_Open()
   Application.Calculation = xlCalculationAutomatic
End Sub

Thanks for the help, again, and the quick response. Have a great holiday weekend!

This thread is solved!