Reflecting all Worksheet Names onto one Worksheet.

[Cameron]

Hi,

Have a slowly growing Workbook containing Dialup Usage reports from my ISP.
There is a "Control" worksheet and a worksheet for each month (now a total
of 16).

I'd like to be able to manipulate the Control worksheet to reflect the names
of the remaining worksheets and handle additional worksheets when they are
introduced.

Eg.

What I'd like to achieve ...

A B C
1 Feb04-Mar04
2 Jan04-Feb04
3 Dec03-Jan04
4 Nov03-Dec03
5 Oct03-Nov03
6 Sep03-Oct03
7


Worksheet order ... "Control Page" / "Feb04-Mar04" / "Jan04-Feb04" /
"Dec03-Jan04" / .....etc...

Then for each corresponding month, acquire stats from that month's page and
include them also. (that's for later)

Any help greatly appreciated.

Cheers,
Cam

 

[JMay]

Why fight it; Here is a VBA solution << paste into a standard module and
run>>

'It will add a sheet to your workbook and add the sheetnames to it.

Sub ListOutSheetNames()
Application.ScreenUpdating = False
Dim Nsheet As Worksheet
Set Nsheet = Sheets.Add
Dim WS As Worksheet
Dim r As Integer
r = 1
For Each WS In Worksheets
If WS.Name <> Nsheet.Name Then
Nsheet.Range("A" & r) = WS.Name
r = r + 1
End If
Next WS
Application.DisplayAlerts = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Hope this helps,,,,,,,,,,,,

 

[BrianB]

I think this is a case where you need to re-consider how you organis
your data.

It depends how you want to use it of course, but, if you want t
analyse it , I suggest that you put everything into a simple table
adding a column for such things as Month or Date. This could be i
Access if it gets too big for Excel. From there it is extremely easy t
analyse data using Pivot Tables etc