Print Sheet/Manual Calcs

[sassriverrat]

Hey- I have a button on a sheet that is *supposed* to print the print area (A1-O55)- fitted to one page, and then convert the sheet to manual calculations (and this sheet only). Well the print part runs perfectly, then I get stuck on the last line with an error 438- Object doesn't support this property or method.

The purpose of this calc- when I add a sheet (via macro) to the workbook, the formulas in it should automatically calculate- then once I hit the print button, they should all *freeze* and not update unless someone hits the button in the calculation menu. This is to prevent updates if a previous reference is changed. Each new sheet should go to manual calcs once it's been printed.

Ideas? Thanks

Sub Print_Sheet()


    ActiveSheet.PageSetup.PrintArea = "$A$1:$O$55"
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = "$A$1:$O$55"
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.7)
        .RightMargin = Application.InchesToPoints(0.7)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
    'ActiveSheet.PrintOut
    ActiveSheet.Calculation = xlCalculationManual
    
End Sub

 

[James_Latimer]

Calculation is application based, not worksheet. You'd have to use the code

Application.Calculation = xlCalculationManual

but this would prevent all formulae within your workbook to not update.

I'd suggest copying the entire sheet that you've printed and convert all cells to values. This way calculation can remain active but the values on the relevant sheet will not update.

 

[sassriverrat]

Hmm. So ideas on maybe a better approach? Each day is a new worksheet- each worksheet has a couple of formulas that reference a first sheet. I basically want all of the formulas on any given sheet to freeze when the sheet is printed (and thereafter closed out) but if someone needs to go back in and modify inputs, a “recalculate” button can be used to rerun formulas on that sheet alone. Maybe there’s a better approach? Maybe I should convert all formulas to a macro, then the recalculate would rerun the macro, right? And the values spitted out wouldn’t update because the macro is a one shot deal, not looping.

 

[shg]

As suggested in https://www.mrexcel.com/forum/excel-questions/1077993-how-freeze-sheet-calculations.html#post5178755

ActiveSheet.EnableCalculation = False

 

[James_Latimer]

As suggested in https://www.mrexcel.com/forum/excel-questions/1077993-how-freeze-sheet-calculations.html#post5178755

ActiveSheet.EnableCalculation = False

Doh!?! Forgot about that, oopsy.

 

[sassriverrat]

I’ll give it a shot. Thanks!