Curious about macro possibility
I think this will accomplish what you're trying to do. But it uses
hyperlinks instead of buttons..
Sub CreateIndexSheet()
Dim ws As Worksheet
Dim wsIndex As Worksheet
Dim lRow As Long
With ActiveWorkbook
Set wsIndex = .Worksheets.Add(Befo=.Worksheets(1))
wsIndex.Name = "Index"
End With
With wsIndex.Range("A1")
.Value = "Index"
.Font.Bold = True
End With
lRow = 2
For Each ws In ActiveWorkbook.Worksheets
If ws.Name < "Index" Then
With wsIndex
.Range("A" & lRow).Value = ws.Name
.Hyperlinks.Add Anchor:=.Range("A" & lRow), _
Address:="", _
SubAddress:=ws.Name & "!A1"
lRow = lRow + 1
End With
End If
Next ws
wsIndex.Columns("A").AutoFit
End Sub
--
Hope that helps.
Vergel Adriano
"WLMPilot" wrote:
I was wondering, and probably reaching for this one, if a macro can write
another macro and place it in the appropriate "Microsoft Excel Object" sheet?
The project I am working on is a annual scheduling workbook that will create
26 sheets (payperiods) when a commandbutton is clicked and name each sheet
accordingly. During this execution, I was wondering if I could also:
1) create 26 commandbuttons with same caption as its respective sheet, size
and align each button on a sheet,
2) write the three line code for each commandbutton, using the caption (also
name of sheet) as the reference to GOTO that sheet?
Just curious about number 2.
Thanks,
Les
|