Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a report with selected rows based on the content in a cell | New Users to Excel | |||
Create Total based on lookup value | Excel Worksheet Functions | |||
how to create and use a new spreadsheet based on a template | Excel Discussion (Misc queries) | |||
How to create a calculated field formula based on Pivot Table resu | Excel Discussion (Misc queries) | |||
Create a total based on multiple conditions is not giving correct. | Excel Worksheet Functions |