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 |
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