I have a spreadsheet that is quite large and has a lot of page formatting as
users select values etc on the input sheet to generate a nice sales package
for the customer.

with careful use of;
Application.Calculation = xlManual
Application.Calculation = xlAutomatic
ActiveSheet.Calculate

I was able to reduce the total calculation and formatting time to
approximately 5 seconds.

However when another sheet is open at the same time which is also
calculation intensive, the total time total calculation and formatting time
is approximately 2 minutes.

I assume that is is because everytime the workbook switches the calculation
mode to automatic, excel recalculates every cell in every workbook? or
something to that effect.

Does anyone have another way? ive tried
ActiveWorkbook.Application.Calculation = xlManual
ActiveWorkbook.Application.Calculation = xlAutomatic
but it hasnt made any difference