This modified version does the cut & paste that you need it to do. Locate
the following lines and make the appropriate modifications to match your
particular spreadsheet coordinates:

1. Set ws1 = wb.Worksheets("Sheet1")
2. Set ws2 = wb.Worksheets("Sheet2")
3. ActiveSheet.PageSetup.PrintArea = "$A$1:$B$1"

Item 1 and Item 2 are the source sheet containing the reference numbers and
3 is the target sheet containing the print range.


Sub PrintRefNums()

Dim wb As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lRow As Long
Dim strRefNum As String

Set wb = ActiveWorkbook
Set ws1 = wb.Worksheets("Sheet1")
Set ws2 = wb.Worksheets("Sheet2")

ws1.Activate
Range("A1").Select
lRow = 1
strRefNum = ActiveCell.Value

Do Until strRefNum = ""
Cells(lRow, 1).Select
strRefNum = ActiveCell.Value
ActiveCell.Cut
ws2.Activate
Range("A1").Select
ActiveSheet.Paste
ActiveSheet.PageSetup.PrintArea = "$A$1:$B$1"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
Collate:=True
lRow = lRow + 1
ws1.Activate
Loop

Set wb = Nothing
Set ws1 = Nothing
Set ws2 = Nothing

End Sub

--
Kevin Backmann


"RobMack" wrote:

Kevin,

This is helps, but I need the reference cells to be cut out individually. I
need the first reference number cut, pasted onto sheet 2, and then sheet 2
printed. Following this I need the second reference number cut, pasted and
printed...Number 3 cut pasted printed... Can you show me how to code this?

"Kevin B" wrote:

You'll have to substitute the print range and the sheet names to the sheet
names and print range you're using. I've used the following values:

SheetWithRefNums is the sheet containing the reference numbers
SheetToPrintFrom is the sheet you're pasting the reference number to and
printing from.
RangeToPrint is the range in SheetToPrintFrom that you're printing

Sub PrintRefNums()

Dim wb As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lRow As Long
Dim strRefNum As String

Set wb = ActiveWorkbook
Set ws1 = wb.Worksheets("SheetWithRefNums")
Set ws2 = wb.Worksheets("SheetToPrintFrom")

ws1.Activate
Range("A1").Select
lRow = 1
strRefNum = ActiveCell.Value

Do Until strRefNum = ""
Cells(lRow, 1).Select
strRefNum = ActiveCell.Value
ws2.Activate
Range("A1").Select
ActiveCell.Value = strRefNum
ActiveSheet.PageSetup.PrintArea = "RangeToPrint"
'ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
Collate:=True
lRow = lRow + 1
ws1.Activate
Loop
Set wb = Nothing
Set ws1 = Nothing
Set ws2 = Nothing

End Sub

--
Kevin Backmann


"RobMack" wrote:

I am wondering how to get a process, contained in Microsoft Excel, to repeat
itself. I need to cut a reference number from sheet one, paste into sheet
two, and then print sheet two. There are multiple reference numbers in sheet
one. I don't know how to get the process to repeat so that all reference
numbers get cut out and then pasted?