Sub Print_Formulas()
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Sheet3" Then
For Each cell In ws.UsedRange
If ws.Range(cell.Address).HasFormula = True Then
LR = Sheet3.Range("A65536").End(xlUp).Address
Sheet3.Range(LR).Offset(1, 0).Value = ws.Name
Sheet3.Range(LR).Offset(1, 1).Value = "'" & ws.Range(cell.Address).Address
Sheet3.Range(LR).Offset(1, 2).Value = "'" & ws.Range(cell.Address).Formula
End If
Next cell
End If
Next ws
End Sub
Sub Print_Formulas()
'GO THROUGH EACH WORKSHEET
For Each ws In ThisWorkbook.Worksheets
'EXCLUDE SHEET3 (USED TO LOG THE FORMULAS)
If ws.Name <> "Sheet3" Then
'GO THROUGH EACH CELL WITHIN THE USED RANGE OF THE SHEET
For Each cell In ws.UsedRange
'IF THE CELL CONTAINS A FORMULA
If ws.Range(cell.Address).HasFormula = True Then
'FIND LAST USED ROW ON SHEET3
LR = Sheet3.Range("A65536").End(xlUp).Address
'PRINT SHEET NAME WHERE FORMULA IS FOUND IN COLUMN A
Sheet3.Range(LR).Offset(1, 0).Value = ws.Name
'PRINT CELL WHERE FORMULA IS FOUND IN COLUMN B
Sheet3.Range(LR).Offset(1, 1).Value = "'" & ws.Range(cell.Address).Address
'PRINT FORMULA IN COLUMN C
Sheet3.Range(LR).Offset(1, 2).Value = "'" & ws.Range(cell.Address).Formula
End If
Next cell
End If
Next ws
End Sub
ADVERTISEMENT
Sub Print_Formulas()
'GO THROUGH EACH WORKSHEET
For Each ws In ThisWorkbook.Worksheets
'EXCLUDE SHEET3 (USED TO LOG THE FORMULAS)
If ws.Name <> "Sheet3" Then
'GO THROUGH EACH CELL WITHIN THE USED RANGE OF THE SHEET
For Each cell In ws.UsedRange
'IF THE CELL CONTAINS A FORMULA
If ws.Range(cell.Address).HasFormula = True Then
'FIND LAST USED ROW ON SHEET3
LR = Sheets("Sheet3").Range("A65536").End(xlUp).Address
'PRINT SHEET NAME WHERE FORMULA IS FOUND IN COLUMN A
Sheets("Sheet3").Range(LR).Offset(1, 0).Value = ws.Name
'PRINT CELL WHERE FORMULA IS FOUND IN COLUMN B
Sheets("Sheet3").Range(LR).Offset(1, 1).Value = "'" & ws.Range(cell.Address).Address
'PRINT FORMULA IN COLUMN C
Sheets("Sheet3").Range(LR).Offset(1, 2).Value = "'" & ws.Range(cell.Address).Formula
End If
Next cell
End If
Next ws
End Sub