Excel Annoyances

HANDY VBA PROCEDURES

CREATE A WORKBOOK WITH A WORKSHEET FOR EACH MONTH

The Annoyance:

I have a group of administrative users who occasionally need to create a workbook with a worksheet for each month of the year. I could do it using a template, but I’d rather automate the process and let them create the workbook by clicking a toolbar button. Is there a VBA procedure I can run that can do this?

The Fix:

The following procedure will do the job. It even names the worksheets with the abbreviated month name and puts the sheets in chronological order!

    Sub YearByMonth()
    Dim intSheetCount As Integer
    Workbooks.Add
    Worksheets.Add Count:=12 - _
    ActiveWorkbook.Worksheets.Count
    For intSheetCount = 1 To 12
    Worksheets(intSheetCount).Name = _
    Format(DateSerial(1, intSheetCount, 1), "mmm")
    Next intSheetCount
   End Sub

PICK RANDOM ENTRIES FROM A LIST

The Annoyance:

I’m setting up auditions for a play and I want to pair the male actors I’ve invited to the second round of auditions with the female actors I’ve called back. The names of the men are stored in cells A2:A8; the names of the women are stored in cells B2:B8. How can I pick entries from two equal-length lists at random, and without repeats?

The Fix:

Well, actually, you need to pick only one set of values at random. If one list changes order, the other one doesn’t have to. You can use this code to solve the problem:

 Sub PickAtRandom() Dim i As Integer, intRank As Integer Randomize Range("B2").Select For i = 8 To 14 ActiveCell.Offset(i, 0).Value ...