Doex Excel do this?

[Mike]

Hi everone,

Say I have a workbook of 30 worksheets, each a worksheet has tab name
and table.

I want to create additional sheet that lists each tab name and its
table's column names!

Is this doable?

Thanks,
Mike

 

[Gord Dibben]

Assuming Table column names are in A1:J1 on each sheet.

Sub CreateListOfSheets()
Dim WS As Worksheet
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "List"
For I = 1 To Worksheets.Count
With Worksheets("List")
Set WS = Worksheets(I)
If WS.Name <> "List" Then
.Cells(I, 1).Value = WS.Name
.Cells(I, 2).Resize(1, 10).Value = WS.Range("A1:J1").Value
End If
End With
Next I
End Sub


Gord Dibben MS Excel MVP