How dynamically create routing slip based on names in specific cel
 

The following macro creates a routing slip for two specific individuals. How
can I change this to create a routing slip based on the names entered in
specific spreadsheet cells?

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 8/8/2005 by Xavier Cougart
'

Sheets("PAF Form").Copy
ActiveWorkbook.HasRoutingSlip = True
With ActiveWorkbook.RoutingSlip
. Recipients = Array("Roger Clemens", "Joe Torry")
. Subject = "Routing: Book1"
. Message = ""
. Delivery = x1OneAfterAnother
. ReturnWhenDone = True
. TrackStatus = True
End With
ActiveWorkbook.Route

Are the cells nice and contiguous?

If yes, you may be able to use something like:

Option Explicit
Sub testme()

Dim myNames As Variant

With Worksheets("sheet1")
myNames = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)).Value
End With

Sheets("PAF Form").Copy
ActiveWorkbook.HasRoutingSlip = False
ActiveWorkbook.HasRoutingSlip = True
With ActiveWorkbook.RoutingSlip
.Recipients = myNames
.Subject = "Routing: Book1"
.Message = ""
.Delivery = xlOneAfterAnother
.ReturnWhenDone = True
.TrackStatus = True
End With
ActiveWorkbook.Route

End Sub

I just picked up the names/addresses from sheet1--starting in A1 to the last
used cell in column A.

If the cells are discontiguous, you could build an array:

Option Explicit
Sub testme2()

Dim myRng As Range
Dim myCell As Range
Dim myNames() As String
Dim iCtr As Long

With Worksheets("sheet1")
Set myRng = .Range("a1:b9,d14,e52")
End With

ReDim myNames(1 To myRng.Cells.Count)

iCtr = 0
For Each myCell In myRng.Cells
iCtr = iCtr + 1
myNames(iCtr) = myCell.Value
Next myCell

ActiveWorkbook.HasRoutingSlip = False
ActiveWorkbook.HasRoutingSlip = True
With ActiveWorkbook.RoutingSlip
.Recipients = myNames
.Subject = "Routing: Book1"
.Message = ""
.Delivery = xlOneAfterAnother
.ReturnWhenDone = True
.TrackStatus = True
End With
ActiveWorkbook.Route

End Sub

And be careful.

This line:
..Delivery = xlOneAfterAnother
had a typo.
It's xl (ex-ell), not ex-one.

arich wrote:

The following macro creates a routing slip for two specific individuals. How
can I change this to create a routing slip based on the names entered in
specific spreadsheet cells?

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 8/8/2005 by Xavier Cougart
'

Sheets("PAF Form").Copy
ActiveWorkbook.HasRoutingSlip = True
With ActiveWorkbook.RoutingSlip
. Recipients = Array("Roger Clemens", "Joe Torry")
. Subject = "Routing: Book1"
. Message = ""
. Delivery = x1OneAfterAnother
. ReturnWhenDone = True
. TrackStatus = True
End With
ActiveWorkbook.Route

--

Dave Peterson