Curious about macro possibility

[WLMPilot]

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

 

[Nigel]

Why not use a template with the controls and code in place? You can read
sheet names, position and change captions and refer to GOTO locations by
reference. I do not see a need to write module code.

 

[Vergel Adriano]

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(Before:=.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