Creating Column headers from Worksheet Names

[Mctabish]

I am trying to create a workbook that has weekly data in a different
worksheet.
I want to have the first page tally and report data from all of the other
sheets. So.... I need to first count the number of sheets, then, to get the
name of each worksheet that is greater than 1, and put that name into the
column based on COLUMN() So, worksheet2 name would be the column header for
Column2, etc....

Thanks!
Mc

 

[Dave Peterson]

You can use a macro that will populate that row with the name of the worksheets.

But it might be simpler to put all your data on one worksheet--and add an
indicator (per row) that would keep track of which "group" that row should be
in.

Option Explicit
Sub testme()
Dim wCtr As Long
With ActiveWorkbook
For wCtr = 2 To .Worksheets.Count
.Worksheets(1).Cells(1, wCtr).Value = "'" & .Worksheets(wCtr).Name
Next wCtr
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm