Printing a worksheet with and without formulas showing

[Ineveraccepttheterms]

In Excel 2007 I have a workbook finished and I want to print the worksheets
with and without the formulas showing.
When I set up the worksheet to print without the formulas and then print all
is well, then when I hit CTRL+` to shoe the formulas, all of the column
widths grow to about triple the original size and then messes up the
printout. Yes I can then re-fit all the columns manually, but then when I go
back to the page without the formulas now it is about 3 times smaller than it
was originally.

What I would like to be able to do/have happen is having the column width
remain the same when I switch back and forth so that the printed page will
look the same without having to re-size each page each time. the formulas are
short and fit in the same space as the values, there is no need for the page
to grow in order to accommodate the display of the formulas so I don't get
why it is doing it and more importantly how do I adjust/prevent the changing
when switching?

 

[Gord Dibben]

Move the formulas, values and cell addresses to a new sheet and print that.

This macro will do that for you.

Sub ListFormulas()
'from John Walkenbach
Dim FormulaCells As Range, Cell As Range
Dim FormulaSheet As Worksheet
Dim Row As Integer
Dim WS As Worksheet
' Create a Range object for all formula cells
On Error Resume Next
Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)

' Exit if no formulas are found
If FormulaCells Is Nothing Then
MsgBox "No Formulas."
Exit Sub
End If

' Add a new worksheet
Application.ScreenUpdating = False
Set FormulaSheet = ActiveWorkbook.Worksheets.Add
FormulaSheet.Name = "Formulas in " & FormulaCells.Parent.Name

' Set up the column headings
With FormulaSheet
Range("A1") = "Address"
Range("B1") = "Formula"
Range("C1") = "Value"
Range("A1:C1").Font.Bold = True
End With

' Process each formula
Row = 2
For Each Cell In FormulaCells
Application.StatusBar = Format((Row - 1) / FormulaCells.Count, "0%")
With FormulaSheet
Cells(Row, 1) = Cell.Address _
(RowAbsolute:=False, ColumnAbsolute:=False)
Cells(Row, 2) = " " & Cell.Formula
Cells(Row, 3) = Cell.Value
Row = Row + 1
End With
Next Cell

' Adjust column widths
FormulaSheet.Columns("A:C").Cells.WrapText = True 'AutoFit
Application.StatusBar = False
End Sub


Gord Dibben MS Excel MVP