specifies in detail how many references the book contains (on new sheet / page) Free Excel Macros (VBA) | Excel Programs

'For online Excel courses click: https://www.excelsizeyeter.com/excelwithkenan.com/udemy_giris.php 
 
Option Explicit

Public Sub ListNamesInWorkbook()
        ' by J.E. McGimpsey
        ' Thanks to Tom Ogilvy for help with overflow.
        Const SHEETNAME As String = "Names in *"
        Const ROWLIM As Long = 65500
        Dim nameSht As Worksheet
        Dim destRng As Range
        Dim cell As Range
        Dim wkSht As Worksheet
        Dim shCnt As Long
        Dim i As Long
        Dim oldScreenUpdating As Boolean
        
        With Application
            oldScreenUpdating = .ScreenUpdating
            .ScreenUpdating = False
        End With
        shCnt = 0
        ListNamesAddSheet nameSht, shCnt
        ' list Workbook-level names
        Set destRng = nameSht.Range("A5")
        With destRng.Offset(-1, 0)
            .Value = "Workbook-Level names"
            .Font.Bold = True
        End With
        With ActiveWorkbook.Names
            If .Count Then
                destRng.Offset(0, 1).ListNames  'only workbook level
                Set destRng = destRng.Offset(0, 1).End(xlDown).Offset(1, -1)
            Else
                destRng.Offset(0, 1).Value = "None"
                Set destRng = destRng.Offset(0, 1)
            End If
        End With
        With destRng.Resize(1, 3).Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = 5
        End With
        Set destRng = destRng.Offset(1, 0)
        For Each wkSht In ActiveWorkbook.Worksheets
            With destRng
                .Value = "Names in sheet """ & wkSht.Name & """"
                .Font.Bold = True
                Set destRng = .Offset(1, 0)
            End With
            With wkSht.Names
                If .Count Then
                    For i = 1 To .Count
                        With .Item(i)
                            destRng.Offset(0, 1) = Mid(.Name, InStr(.Name, "!") + 1)
                            destRng.Offset(0, 2) = "'" & .RefersTo
                            Set destRng = destRng.Offset(1, 0)
                            If destRng.Row > ROWLIM Then
                                ListNamesAddSheet nameSht, shCnt
                                Set destRng = nameSht.Range("A5")
                                destRng.Offset(-1, 0).Value = _
                                    "Names in sheet """ & wkSht.Name & """"
                            End If
                        End With
                    Next i
                Else
                    destRng.Offset(0, 1).Value = "None"
                    Set destRng = destRng.Offset(1, 0)
                End If
            End With
            With destRng.Resize(1, 4).Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = 5
            End With
            Set destRng = destRng.Offset(1, 0)
        Next wkSht
        With Application
            .StatusBar = False
            .ScreenUpdating = oldScreenUpdating
        End With
    End Sub

Private Sub ListNamesAddSheet( _
                nameSht As Worksheet, shtCnt As Long)
        Const SHEETNAME As String = "Names in "
        Const SHEETTITLE As String = "Names in $ as of "
        Const DATEFORMAT As String = "dd MMM yyyy hh:mm"
        Dim shtName As String

With ActiveWorkbook
            ' Delete existing sheet and create new one
            shtName = Left(SHEETNAME & .Name, 28)
            shtCnt = shtCnt + 1
            If shtCnt > 1 Then _
                shtName = shtName & "_" & Format(shtCnt, "00")
             On Error Resume Next
            Application.DisplayAlerts = False
            .Worksheets(shtName).Delete
            Application.DisplayAlerts = True
            On Error GoTo 0
            Set nameSht = .Worksheets.Add( _
                    after:=Sheets(Sheets.Count))
        End With

With nameSht
            ' Format headers
            .Name = shtName
            .Columns(1).ColumnWidth = 30
            .Columns(2).ColumnWidth = 20
            .Columns(3).ColumnWidth = 90
            With .Range("B:C")
                .Font.Size = 9
                .HorizontalAlignment = xlLeft
                .EntireColumn.WrapText = True
            End With
            With .Range("A1")
                .Value = Application.Substitute(SHEETTITLE, "$", _
                        ActiveWorkbook.Name) & Format(Now, DATEFORMAT)
                With .Font
                    .Bold = True
                    .ColorIndex = 5
                    .Size = 14
                End With
            End With
            With .Range("A3").Resize(1, 3)
                .Value = Array("Sheet", "Name", "Refers To")
                With .Font
                    .ColorIndex = 13
                    .Bold = True
                    .Size = 12
                End With
                .HorizontalAlignment = xlCenter
                With .Borders(xlEdgeBottom)
                    .LineStyle = xlDouble
                    .Weight = xlThick
                    .ColorIndex = 5
                End With
            End With
        End With
    End Sub

No	Macro Name
1	specifies in detail how many references the book contains (on new sheet / page)
2	specifies in detail how many formulas are in the book (on new sheet / page)
3	excel operators hk
4	inserts months as sheet / pages Specifies days in detail
5	remove sheet / page protections
6	specify the number of copies to print from the printer
7	lists the named cells in detail on the wb_names sheet / page
8	save book you specify name - notifies you by message
9	records detailed formatted cells on the records sheet / page
10	list formulas on new sheet / page
11	Inputbox add year as summer
12	Determining the name of the sheet / page and file containing the active cell
13	Double-click any cell in column b to specify how many rows to insert
14	finds formulas on active sheet / page lists new sheet / page
15	dialog box without userform
16	show file path list in detail fields of files file path etcâ € ¦
17	printing and prompting
18	Highlighted Named Ranges: If you are not sure how many named ranges are in your worksheet, you can use this code to highlight them all.
19	Find and select the remaining cell in the value between 2 selected numbers
20	Finding the Number of Each Item in the List Using the VBA and Pivot Table

Excel Free Macros (VBA)

Specifies İn Detail How Many References The Book Contains (on New Sheet / Page)

Online Excel Courses

Services

How can we help you?

Subscribe to our Youtube Channel?

Keywords