ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Dynamic list based on sheet names (https://www.excelbanter.com/excel-programming/416934-dynamic-list-based-sheet-names.html)

sgltaylor

Dynamic list based on sheet names
 
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

Dynamic list based on sheet names
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"sgltaylor" wrote in message
...
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



Kent Prokopy

Dynamic list based on sheet names
 
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

"sgltaylor" wrote:

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



All times are GMT +1. The time now is 11:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com