gtd526
Well-known Member
- Joined
- Jul 30, 2013
- Messages
- 657
- Office Version
- 2019
- Platform
- Windows
Hello,
I'm looking for the worksheet name of the sheet being accessed to get the formulas (results = A1).
Here is the code I'm using:
I'm looking for the worksheet name of the sheet being accessed to get the formulas (results = A1).
Here is the code I'm using:
VBA Code:
Sub ListFormulas()
Dim Rng As Range
Dim WorkRng As Range
Dim xSheet As Worksheet
Dim xRow As Integer
' xTitleId = "Range Of Cells for Formula"
Set WorkRng = Range("A1:Z100")
' Set WorkRng = Application.Selection
' Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlFormulas, 23)
If WorkRng Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Set xSheet = Application.ActiveWorkbook.Worksheets.Add
[B]' xSheet.Range("A1") = ActiveSheet.Name 'not working
' xSheet.Range("A1") = WorkRng.ActiveSheet.Name 'not working[/B]
xSheet.Range("A2:C2") = Array("Address", "Formula", "Value")
xSheet.Range("A2:C2").Font.Bold = True
xRow = 3
For Each Rng In WorkRng
xSheet.Cells(xRow, 1) = Rng.Address(RowAbsolute:=False, ColumnAbsolute:=False)
xSheet.Cells(xRow, 2) = " " & Rng.Formula
xSheet.Cells(xRow, 3) = Rng.Value
xRow = xRow + 1
Next
xSheet.Columns("A:C").AutoFit
Application.ScreenUpdating = True
End Sub