Macros project
 

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?

Macros project
 

Are you talking about using a Macro or VBA? It would seem you need a Loop to
achieve this, but I believe we would need more information about exactly what
your looking for.


"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?

Macros project
 

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?

Macros project
 

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?

Macros project
 

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?

Macros project
 

Kevin,

Thanks for helping! This process has helped, and the code works well. I
was wondering if you might be able to help with one more thing: Saving the
documents after printing, allowing for different file names(save as).

Rob

"Kevin B" wrote:

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?

Macros project
 

Sorry for the delay, but I didn't have an opportunity to run through my
former posts.

If the save thing is still an issue you can try the following:
================================================== ====
Sub SaveAsOnPrint()

Dim varFileName As Variant

varFileName = Application.GetSaveAsFilename

If varFileName = False Then Exit Sub

ActiveWorkbook.SaveAs Filename:=varFileName

End Sub
================================================== ====
--
Kevin Backmann


"RobMack" wrote:

Kevin,

Thanks for helping! This process has helped, and the code works well. I
was wondering if you might be able to help with one more thing: Saving the
documents after printing, allowing for different file names(save as).

Rob

"Kevin B" wrote:

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?