Dynamic list based on sheet names

[sgltaylor]

Hi,

Any help with the following would be appreciated.

I have a workbook with 15 worksheets.
Worksheet 1 is called "Summary".
Worksheet 2 is called "A".
Worksheets 3 to 15 are named "Jan to Dec" respectively.
Worksheet 15 is called "B".

The names of the worksheets between the "A" and "B" worksheets
are listed on the "Summary" worksheet in cell C2 down. I need to make
this list dynamic. For example, when the user moves the "B" worksheet
in-between the worksheet called "Jun" and the worksheet called "Jul",
I need the list on the "Summary" worksheet to update to show only
the months Jan to Jun. This is assuming the "A" worksheet is to the
left of the "Jan" worksheet.

Should the user move the "A" worksheet in-between the "Jan" and
"Feb" worksheets and the "B" worksheet between the "April" and May"
worksheets, the list on the "Summary" worksheet should be updated
to show "Feb" to "April" etc.

Any ideas?

Thanks,

Steve

 

[Don Guillett]

If desired, send your workbook to my address below and I will take a look.

 

[Kent Prokopy]

Private Sub CommandButton1_Click()
Dim x As Integer
Dim s As Worksheet

Sheets("Summary").Range("A1:A12").Clear

x = 0
For Each s In Sheets
If s.Name <> "Summary" Then
If s.Name = "A" Then
x = 1
Else
If x >= 1 Then
If s.Name = "B" Then Exit Sub
Sheets("Summary").Range(Cells(x, 1).Address).Value = s.Name
x = x + 1
End If
End If
End If
Next s

End Sub