hello
this excel vba macro create a list of the formulas in every visible worksheet in an excel workbook.how can i prevent the macro from showing hidden formulas?
Sub ListFormulasineveryactivesheet()
Dim FormulaCells As Range, cell As Range
Dim FormulaSheet As Worksheet
Dim ws As Worksheet
Dim Row As Integer
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
If ws.Visible = xlSheetVisible Then
Set FormulaCells = ws.Range("A1").SpecialCells(xlFormulas, 23)
If FormulaCells Is Nothing Then
MsgBox "No Formulas cells in :" & ws.name, vbInformation, "a message"
Else
Application.ScreenUpdating = False
Set FormulaSheet = Workbooks.Add(xlWorksheet)
FormulaSheet.name = "Formulas in " & ws.name
With FormulaSheet
[A1].Resize(, 5).Value = Array("address", "formula", "value", "name", "#")
Range("A1:E1").Font.Bold = True
End With
Row = 2
For Each cell In FormulaCells
With FormulaSheet
Cells(Row, 1) = cell.Address _
(RowAbsolute:=False, ColumnAbsolute:=False)
Cells(Row, 2) = " " & cell.Formula
Cells(Row, 3) = Format(cell.Value, "#,##0.00")
Cells(Row, 4) = ws.name
Cells(Row, 5) = "=rows(R1C:R[-1]C)"
Row = Row + 1
End With
Set FormulaCells = Nothing
Next cell
FormulaSheet.Columns("A:E").autofit
FormulaSheet.name = "Formulas in " & ws.name
End If
End If
Next
End Sub