Code:
Option Explicit
Dim Ar() As String
Sub Tst()
Dim Ws As Worksheet, i As Integer
Dim Ch As Chart
With ShTst
.Activate
.Cells.Clear
End With
i = 0
For Each Ws In ThisWorkbook.Worksheets
i = i + 1
With ShTst
.Range("A" & i) = Ws.Name
.Range("B" & i) = Ws.CodeName
End With
Next Ws
For Each Ch In ThisWorkbook.Charts
i = i + 1
With ShTst
.Range("A" & i) = Ch.Name
.Range("B" & i) = Ch.CodeName
End With
Next Ch
End Sub
Sub Tst2()
With ShTst
.Activate
.Cells.Clear
End With
ShTst.Range("A1:A" & UBound(Ar) + 1) = NameSheets
End Sub
Private Function NameSheets()
Dim i As Integer
ReDim Ar(ThisWorkbook.Sheets.Count - 1)
For i = 0 To ThisWorkbook.Sheets.Count - 1
Ar(i) = Sheets(i + 1).Name
Next i
NameSheets = Application.WorksheetFunction.Transpose(Ar)
End Function
Bookmarks