Printing formulas


Board Regular
Feb 21, 2002
Hope someone can help...i have a workbook with several worksheets. i know that by pressing the CRl key and ~ key i can see part of all formulas on the sheet, but what i want to accomplish is to create a report of all formulas in the workbook so that i can print. The report should print by given the sheet name, cell, and the whole formula versus just part of it.

Any ideas..Roy

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().


Board Regular
Mar 21, 2002
This will list formulas , cell address, and valves, for a sheet, in a new worksheet,
I think I got it off this board but don’t know who to give the credit to. As is you would have to do each sheet but maybe it could be modified to do the whole workbook at one time

Sub ListFormulas()
Dim FormulaCells As Range, Cell As Range
Dim FormulaSheet As Worksheet
Dim Row As Integer

' 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 or the sheet is protected."
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
Application.StatusBar = False
End Sub


Well-known Member
Jul 27, 2002

Following code will do what You want:

<FONT color=blue>Sub </FONT>ListFormulas()

<FONT color=blue>Dim </FONT>FormulaCells<FONT color=blue> As</FONT> Range, Cell<FONT color=blue> As</FONT> Range

<FONT color=blue>Dim </FONT>FormulaSheet<FONT color=blue> As</FONT> Worksheet

<FONT color=blue>Dim </FONT>wsSheet<FONT color=blue> As</FONT> Worksheet

<FONT color=blue>Dim </FONT>stName<FONT color=blue> As</FONT><FONT color=blue> String</FONT>

<FONT color=#ff0000>'Revised
<FONT color=blue>Dim </FONT>Row<FONT color=blue> As</FONT><FONT color=blue> Long</FONT>

stName = "Formulas in " & ActiveWorkbook.Name

<FONT color=blue>With </FONT>Application

.ScreenUpdating =<FONT color=blue> False</FONT>

.DisplayAlerts =<FONT color=blue> False</FONT>

<FONT color=blue>End With</FONT>

<FONT color=blue>On Error</FONT> <FONT color=blue>Resume </FONT>Next


<FONT color=blue>On Error</FONT> <FONT color=blue>GoTo</FONT> 0

<FONT color=blue>Set </FONT>FormulaSheet = ActiveWorkbook.Worksheets.Add

<FONT color=#ff0000>'Revised
FormulaSheet.Name = stName

<FONT color=blue>With </FONT>FormulaSheet

.Range("A1").Value = "Worksheet Name"

.Range("B1").Value = "Address"

.Range("C1").Value = "Formula"

.Range("D1").Value = "Value"

.Range("A1:D1").Font.Bold =<FONT color=blue> True</FONT>

<FONT color=blue>End With</FONT>

<FONT color=#ff0000>'Revised to loop through all the worksheets and set the
<FONT color=#ff0000>'reference to each worksheet.

Row = 2

<FONT color=blue>For </FONT>Each wsSheet In ActiveWorkbook.Worksheets

<FONT color=blue>If </FONT>wsSheet.Name <> stName Then

<FONT color=blue>On Error</FONT> <FONT color=blue>GoTo</FONT> SheetNext

<FONT color=blue>With </FONT>wsSheet

<FONT color=blue>Set </FONT>FormulaCells = .Range("A1").SpecialCells(xlFormulas, 23)

<FONT color=blue>End With</FONT>

<FONT color=blue>For </FONT>Each Cell In FormulaCells

<FONT color=blue>With </FONT>FormulaSheet

Cells(Row, 1) = wsSheet.Name

Cells(Row, 2) = Cell.Address _

(RowAbsolute:=False, ColumnAbsolute:=False)

Cells(Row, 3) = " " & Cell.Formula

Cells(Row, 4) = Cell.Value

Row = Row + 1

<FONT color=blue>End With</FONT>

<FONT color=blue>Next </FONT>Cell

<FONT color=blue>End If</FONT>


<FONT color=blue>Next </FONT>wsSheet


<FONT color=blue>With </FONT>Application

.ScreenUpdating =<FONT color=blue> True</FONT>

.DisplayAlerts =<FONT color=blue> True</FONT>

<FONT color=blue>End With</FONT>

<FONT color=blue>End Sub</FONT>

Kind regards,

Watch MrExcel Video

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back