Thank you...thank you...Bgeier and somethinglikeant. I only wish I knew VBA
to accomplish so many of these type of tasks. While the "record macro" gets
one started it does have its draw backs for using the macro again when lists
are involved.

Jan

"bgeier" wrote:


Try this.
It is a combination of your code (looks like a recorded macro) and
somethinglikeant's code.

I have tried to explain the steps, but if you need any clarification,
do not hesitate to post.

Option Explicit

Sub Order()
Dim intCounter As Integer
Dim dblActiveRow As Double

Cells(2, 21).Select 'does the same as your Range("U2").Select code
'Start the loop since you are looking for 1 or 2 or 3, you only
need to count from 1 to 3
For intCounter = 1 To 3
'This is the same code as your find, I just removed the
unnecessary prompts
Cells.Find(intCounter, ActiveCell, xlFormulas, xlWhole,
xlByColumns, xlNext, False, False).EntireRow.Select
'Insert the row
Selection.EntireRow.Insert
'You only want to add the color to columns A to R. so you need
to store the row number to define the range
dblActiveRow = ActiveCell.Row
'Select column "A" through "R" on the selected row (A = 1, R =
18)
Range(Cells(dblActiveRow, 1), Cells(dblActiveRow, 18)).Select
'Insert Page Break
ActiveSheet.HPageBreaks.Add Befo=ActiveCell
'Format the selection
With Selection
.Interior.ColorIndex = 41
.Font.ColorIndex = 2
.Font.Name = "Arial"
.Font.Bold = True
.Font.Italic = True
.Font.Size = 14
End With
'This decides which "header" goes with each search
Select Case intCounter
Case 1: Cells(dblActiveRow, 2) = "Mike"
Case 2: Cells(dblActiveRow, 2) = "MAM"
Case 3: Cells(dblActiveRow, 2) = "To Printer"
End Select
Next intCounter
'Make the columns only as wide as needed
Columns.AutoFit
'Make the rows only as tall as needed
Rows.AutoFit
End Sub


--
bgeier
------------------------------------------------------------------------
bgeier's Profile: http://www.excelforum.com/member.php...o&userid=12822
View this thread: http://www.excelforum.com/showthread...hreadid=541689