Indexing an added sheet.
Posted by Pete on September 18, 2001 11:00 PM
To anyone who can help.
I have a macro which contains the lines:
Dim ws As Worksheet, x As Integer
x = 1
For Each ws In Worksheets
If Left(ws.Name, 8) = Format(Now(), "dd-mm-yy") Then x = x + 1
Next
Sheets.Add.Name = Format(Now(), "dd-mm-yy") & "(" & x & ")"
I wish to index these new sheets on a page called "index". I need a button which links to that page and each time I create a new sheet using the macro above I need to have the button down below the last.
Thanx.
Posted by Liam on September 19, 2001 3:54 AM
Instead of creating an index page to navigate to your worksheets, if you right click on any of the navigation arrows at the bottom left of the window, a list of the worksheets in the workbook will appear and you can activate any worksheet on the list by selecting it.
If you really want to create an index page, would suggest that you just create a list of the worksheets and when one of the cells in the list is selected, that worksheet is activated.
To do this, add the following line to the code you posted :-
Worksheets("Index").Range("A65536").End(xlUp).Offset(1, 0).Value = Format(Now(), "dd-mm-yy") & "(" & x & ")"
And put the following in the "Index" worksheet code module :-
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = Range(Range("A1"), Range("A65536").End(xlUp))
If Not Intersect(rng, Target) Is Nothing Then Worksheets(Target.Value).Activate
End Sub
