Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic List of names? | Excel Worksheet Functions | |||
macro to take worksheet names and create dynamic list/range | Excel Programming | |||
Generate sheet names from list, assign data to summary sheet. | Excel Programming | |||
Retrieving value from list based on dynamic condition | Excel Discussion (Misc queries) | |||
Can you sort a list based on catagory names without useing a custom list? | Excel Programming |