Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Workbook index
I have developed a workbook, used as a route list, containing as many as 50
to 60 worksheets. Each of the worksheets has customer details and delivery information including time of delivery, which can change from day to day as customers are added or removed. In order to complete the workbook, an index of the worksheets is needed. As an example, the first worksheet would contain a list of the tabs used to complete the route, and provide the total of the tabs serviced on the route. Any help would be appreciated. Thanks. Wally |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Workbook index
This macro will give you a list of sheets.
Private Sub ListSheets() 'list of sheet names starting at A1 on new sheet Dim rng As Range Dim i As Integer Worksheets.Add(After:=Worksheets(Worksheets.Count) ).Name = "List" Set rng = Range("A1") For Each Sheet In ActiveWorkbook.Sheets rng.Offset(i, 0).Value = Sheet.Name i = i + 1 Next Sheet End Sub What else you need is unclear to me. Gord Dibben MS Excel MVP On Sat, 1 Dec 2007 10:01:00 -0800, wally wrote: I have developed a workbook, used as a route list, containing as many as 50 to 60 worksheets. Each of the worksheets has customer details and delivery information including time of delivery, which can change from day to day as customers are added or removed. In order to complete the workbook, an index of the worksheets is needed. As an example, the first worksheet would contain a list of the tabs used to complete the route, and provide the total of the tabs serviced on the route. Any help would be appreciated. Thanks. Wally |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Workbook index
Wally
I agree with Gord. You didn't give us any information regarding what sheets go with what route and what customers go with what route and how such could be determined. Also define what you call an "index of the worksheets". Imagine that you are doing this by hand, then give us a step by step of what you do. HTH Otto "wally" wrote in message ... I have developed a workbook, used as a route list, containing as many as 50 to 60 worksheets. Each of the worksheets has customer details and delivery information including time of delivery, which can change from day to day as customers are added or removed. In order to complete the workbook, an index of the worksheets is needed. As an example, the first worksheet would contain a list of the tabs used to complete the route, and provide the total of the tabs serviced on the route. Any help would be appreciated. Thanks. Wally |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Workbook index
Im sorry I didnt do a good job of describing my project. First of all, you
must understand this is only the second time I have attempted to include macros in my work. The first was when Gord helped me with this project when I asked for help on 2 Nov 2007 in Auto fill multiple worksheets. His help at that time did the trick, and I am now able to handle that. The application I have designed is an effort to assist local law enforcement by senior volunteers accomplishing daily vacation house checks while the residents are away. We are replacing a paper system with a laptop to record the daily activity including the time and identity of the volunteer officers making the check. Each sheet tab name is the address of the property to be checked. The sheets contain information providing the name of the property owner, telephone contacts, authorized contacts, and other information. All of this information is protected against accidental revision. The application is set up to be run on bi-weekly periods, so the information can be transferred to a permanent record on the citys computer. As you can imagine, the addresses change as the residents come and go. The sheets are arranged in the order the checks are to be made, so it is necessary to insert new sheets (addresses) as the route order changes. The worksheet index is an attempt to visualize all the addresses on a single sheet, probably at the beginning of the workbook. By using the count function on the sheet added by Gords macro, the number of addresses can be determined. The number of properties checked each day is used in our monthly and annual report to the city. I really appreciate the help of you fellows and the learning experience I have gained through that help. This is a great service! If additional details are needed to clarify the project, please contact me. Wally "Otto Moehrbach" wrote: Wally I agree with Gord. You didn't give us any information regarding what sheets go with what route and what customers go with what route and how such could be determined. Also define what you call an "index of the worksheets". Imagine that you are doing this by hand, then give us a step by step of what you do. HTH Otto "wally" wrote in message ... I have developed a workbook, used as a route list, containing as many as 50 to 60 worksheets. Each of the worksheets has customer details and delivery information including time of delivery, which can change from day to day as customers are added or removed. In order to complete the workbook, an index of the worksheets is needed. As an example, the first worksheet would contain a list of the tabs used to complete the route, and provide the total of the tabs serviced on the route. Any help would be appreciated. Thanks. Wally |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Workbook index
Wally
This macro should get you started. I didn't know if you wanted to copy any data other than the sheet names so I gave you remarked-out code for other data. I assumed the name of the listing sheet to be "List". Change that as needed. The macro, as written, clears the List sheet except for row 1. HTH Otto Sub ListSheets() Dim Dest As Range Dim ws As Worksheet Sheets("List").Select 'Assume row 1 is a header row in the List sheet 'Assume 5 columns of data in List sheet If Not IsEmpty(Range("A2").Value) Then _ Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 5).ClearContents Set Dest = Range("A2") For Each ws In ActiveWorkbook.Sheets If ws.Name = "List" Then GoTo Nextws Dest.Value = ws.Name 'Copy other data from each sheet as needed, for instance: 'With ws 'Dest.Offset(, 1).Value = .Range("D4").Value 'End With Set Dest = Dest.Offset(1) Nextws: Next ws End Sub "wally" wrote in message ... I'm sorry I didn't do a good job of describing my project. First of all, you must understand this is only the second time I have attempted to include macros in my work. The first was when Gord helped me with this project when I asked for help on 2 Nov 2007 in "Auto fill multiple worksheets". His help at that time did the trick, and I am now able to handle that. The application I have designed is an effort to assist local law enforcement by senior volunteers accomplishing daily vacation house checks while the residents are away. We are replacing a paper system with a laptop to record the daily activity including the time and identity of the volunteer officers making the check. Each sheet tab name is the address of the property to be checked. The sheets contain information providing the name of the property owner, telephone contacts, authorized contacts, and other information. All of this information is protected against accidental revision. The application is set up to be run on bi-weekly periods, so the information can be transferred to a permanent record on the city's computer. As you can imagine, the addresses change as the residents come and go. The sheets are arranged in the order the checks are to be made, so it is necessary to insert new sheets (addresses) as the route order changes. The worksheet index is an attempt to visualize all the addresses on a single sheet, probably at the beginning of the workbook. By using the 'count' function on the sheet added by Gord's macro, the number of addresses can be determined. The number of properties checked each day is used in our monthly and annual report to the city. I really appreciate the help of you fellows and the learning experience I have gained through that help. This is a great service! If additional details are needed to clarify the project, please contact me. Wally "Otto Moehrbach" wrote: Wally I agree with Gord. You didn't give us any information regarding what sheets go with what route and what customers go with what route and how such could be determined. Also define what you call an "index of the worksheets". Imagine that you are doing this by hand, then give us a step by step of what you do. HTH Otto "wally" wrote in message ... I have developed a workbook, used as a route list, containing as many as 50 to 60 worksheets. Each of the worksheets has customer details and delivery information including time of delivery, which can change from day to day as customers are added or removed. In order to complete the workbook, an index of the worksheets is needed. As an example, the first worksheet would contain a list of the tabs used to complete the route, and provide the total of the tabs serviced on the route. Any help would be appreciated. Thanks. Wally |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a first page index of worksheets in my workbook? | Excel Worksheet Functions | |||
Index & multiple sheets in one workbook | Excel Worksheet Functions | |||
How to force workbook to always open on the index sheet tab | Excel Worksheet Functions | |||
Range Name from Another Workbook conflicts with INDEX and INDIRECT | Excel Worksheet Functions | |||
Index/Match to look up a value in one workbook and insert it into. | Excel Discussion (Misc queries) |