Workbook index

[wally]

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

 

[Gord Dibben]

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

 

[Otto Moehrbach]

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]

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]

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