The information below would be on worksheet named €œSheet1€�
A B C
Cust id Product code Amount

1 10006 RFUUK 2000.00
2 10010 RFUUK 2000.00
3 10030 RFUOS 3000.00


I need help to write a macro to carry out the following actions,

1. I would like to have the information for each row on a specific position
(e.g. A1, A12 and C12) on another worksheet (e.g. named €œSheet2€�) then print
that page.

2. Repeat the above cycle for the each row for the list on €œSheet1€�.

Thanks. Any help appreciated.

"nc" wrote:

The information below would be on worksheet named €œSheet1€�
A B C
Cust id Product code Amount

1 10006 RFUUK 2000.00
2 10010 RFUUK 2000.00
3 10030 RFUOS 3000.00


I need help to write a macro to carry out the following actions,

1. I would like to have the information for each row on a specific position
(e.g. A1, A12 and C12) on another worksheet (e.g. named €œSheet2€�) then print
that page.

2. Repeat the above cycle for the each row for the list on €œSheet1€�.

Thanks. Any help appreciated.

Hi

I cannot view your reply.


"JLatham" wrote:



"nc" wrote:

The information below would be on worksheet named €œSheet1€�
A B C
Cust id Product code Amount

1 10006 RFUUK 2000.00
2 10010 RFUUK 2000.00
3 10030 RFUOS 3000.00


I need help to write a macro to carry out the following actions,

1. I would like to have the information for each row on a specific position
(e.g. A1, A12 and C12) on another worksheet (e.g. named €œSheet2€�) then print
that page.

2. Repeat the above cycle for the each row for the list on €œSheet1€�.

Thanks. Any help appreciated.

It seems the system ate my response! Trying again. This code will copy each
row of information from one sheet ('Sheet1') and place it in the first row of
'Sheet2' and then print Sheet2. It is based on non-blank entries in column A
of Sheet1. You'll probably need to change sheet names referenced in the
code. You didn't show column 'header/label' in row 1 of your example so this
starts copying/printing at row 1 from your first sheet.

To put the code into your workbook: Open the workbook, press [Alt]+[F11] to
get into the VB Editor. Choose Insert | Module from the VBE menu and then
copy and paste the code below into it. Modify as required and close the VBE.
Use
Tools | Macro | Macros to run it. The code...

Sub PrintCustomerData()
Dim srcSheet As Worksheet
Dim destSheet As Worksheet
Dim srcRange As Range
Dim destRange As Range
Dim lastRow As Long
Dim lastCol As Long
Dim rOffset As Long

Set srcSheet = Worksheets("Sheet1") ' change
Set destSheet = Worksheets("Sheet2") ' change

lastRow = srcSheet.Range("A" & Rows.Count).End(xlUp).Row
Do While rOffset < lastRow
If Not IsEmpty(srcSheet.Range("A1").Offset(rOffset, 0)) Then
lastCol = srcSheet.Cells(rOffset + 1, Columns.Count). _
End(xlToLeft).Column
Set srcRange = srcSheet.Range(srcSheet.Cells(rOffset + 1, _
1).Address & ":" & srcSheet.Cells(rOffset + 1, _
lastCol).Address)
'this always puts copy in Row 1 of 2nd sheet, can be changed
Set destRange = destSheet.Range("A1:" & Cells(1, _
lastCol).Address)
destSheet.Cells.Clear ' remove earlier results
destRange.Value = srcRange.Value ' transfer data
destSheet.PrintOut copies:=1
End If
rOffset = rOffset + 1
Loop
MsgBox "Job completed"
End Sub


"nc" wrote:

Hi

I cannot view your reply.


"JLatham" wrote:



"nc" wrote:

The information below would be on worksheet named €œSheet1€�
A B C
Cust id Product code Amount

1 10006 RFUUK 2000.00
2 10010 RFUUK 2000.00
3 10030 RFUOS 3000.00


I need help to write a macro to carry out the following actions,

1. I would like to have the information for each row on a specific position
(e.g. A1, A12 and C12) on another worksheet (e.g. named €œSheet2€�) then print
that page.

2. Repeat the above cycle for the each row for the list on €œSheet1€�.

Thanks. Any help appreciated.

Thanks.
What changes do I need to make to the macro, if the column 'header/label' is
in row 1, and the data were placed on sheet2 in specific cell i.e A1, A12 and
C12, rather than the first row on 'Sheet2'. For example

On sheet2

Cust id in A1
Product code in A12
Amount in C12


"JLatham" wrote:

It seems the system ate my response! Trying again. This code will copy each
row of information from one sheet ('Sheet1') and place it in the first row of
'Sheet2' and then print Sheet2. It is based on non-blank entries in column A
of Sheet1. You'll probably need to change sheet names referenced in the
code. You didn't show column 'header/label' in row 1 of your example so this
starts copying/printing at row 1 from your first sheet.

To put the code into your workbook: Open the workbook, press [Alt]+[F11] to
get into the VB Editor. Choose Insert | Module from the VBE menu and then
copy and paste the code below into it. Modify as required and close the VBE.
Use
Tools | Macro | Macros to run it. The code...

Sub PrintCustomerData()
Dim srcSheet As Worksheet
Dim destSheet As Worksheet
Dim srcRange As Range
Dim destRange As Range
Dim lastRow As Long
Dim lastCol As Long
Dim rOffset As Long

Set srcSheet = Worksheets("Sheet1") ' change
Set destSheet = Worksheets("Sheet2") ' change

lastRow = srcSheet.Range("A" & Rows.Count).End(xlUp).Row
Do While rOffset < lastRow
If Not IsEmpty(srcSheet.Range("A1").Offset(rOffset, 0)) Then
lastCol = srcSheet.Cells(rOffset + 1, Columns.Count). _
End(xlToLeft).Column
Set srcRange = srcSheet.Range(srcSheet.Cells(rOffset + 1, _
1).Address & ":" & srcSheet.Cells(rOffset + 1, _
lastCol).Address)
'this always puts copy in Row 1 of 2nd sheet, can be changed
Set destRange = destSheet.Range("A1:" & Cells(1, _
lastCol).Address)
destSheet.Cells.Clear ' remove earlier results
destRange.Value = srcRange.Value ' transfer data
destSheet.PrintOut copies:=1
End If
rOffset = rOffset + 1
Loop
MsgBox "Job completed"
End Sub


"nc" wrote:

Hi

I cannot view your reply.


"JLatham" wrote:



"nc" wrote:

The information below would be on worksheet named €œSheet1€�
A B C
Cust id Product code Amount

1 10006 RFUUK 2000.00
2 10010 RFUUK 2000.00
3 10030 RFUOS 3000.00


I need help to write a macro to carry out the following actions,

1. I would like to have the information for each row on a specific position
(e.g. A1, A12 and C12) on another worksheet (e.g. named €œSheet2€�) then print
that page.

2. Repeat the above cycle for the each row for the list on €œSheet1€�.

Thanks. Any help appreciated.

I think you'll find this will do that for you - starts looking at row 2 on
Sheet1 all the way down, and any row with something in column A gets column A
value put into A1 on Sheet2, value in column B into A12 and value in column C
into C12.

Sub PrintCustomerData()
Dim srcSheet As Worksheet
Dim destSheet As Worksheet
Dim baseCell As Range
Dim lastRow As Long
Dim rOffset As Long

Set srcSheet = Worksheets("Sheet1") ' change
Set destSheet = Worksheets("Sheet2") ' change

lastRow = srcSheet.Range("A" & Rows.Count).End(xlUp).Row
Set baseCell = srcSheet.Range("A1")
'initialize rOffset to look in row 2 first
rOffset = 1
Do While rOffset <= lastRow
If Not IsEmpty(baseCell.Offset(rOffset, 0)) Then
destSheet.Range("A1") = baseCell.Offset(rOffset, 0) ' col A
destSheet.Range("A12") = baseCell.Offset(rOffset, 1) ' col B
destSheet.Range("C12") = baseCell.Offset(rOffset, 2) ' col C
destSheet.PrintOut copies:=1
End If
rOffset = rOffset + 1
Loop
MsgBox "Job completed"
End Sub



"nc" wrote:

Thanks.
What changes do I need to make to the macro, if the column 'header/label' is
in row 1, and the data were placed on sheet2 in specific cell i.e A1, A12 and
C12, rather than the first row on 'Sheet2'. For example

On sheet2

Cust id in A1
Product code in A12
Amount in C12


"JLatham" wrote:

It seems the system ate my response! Trying again. This code will copy each
row of information from one sheet ('Sheet1') and place it in the first row of
'Sheet2' and then print Sheet2. It is based on non-blank entries in column A
of Sheet1. You'll probably need to change sheet names referenced in the
code. You didn't show column 'header/label' in row 1 of your example so this
starts copying/printing at row 1 from your first sheet.

To put the code into your workbook: Open the workbook, press [Alt]+[F11] to
get into the VB Editor. Choose Insert | Module from the VBE menu and then
copy and paste the code below into it. Modify as required and close the VBE.
Use
Tools | Macro | Macros to run it. The code...

Sub PrintCustomerData()
Dim srcSheet As Worksheet
Dim destSheet As Worksheet
Dim srcRange As Range
Dim destRange As Range
Dim lastRow As Long
Dim lastCol As Long
Dim rOffset As Long

Set srcSheet = Worksheets("Sheet1") ' change
Set destSheet = Worksheets("Sheet2") ' change

lastRow = srcSheet.Range("A" & Rows.Count).End(xlUp).Row
Do While rOffset < lastRow
If Not IsEmpty(srcSheet.Range("A1").Offset(rOffset, 0)) Then
lastCol = srcSheet.Cells(rOffset + 1, Columns.Count). _
End(xlToLeft).Column
Set srcRange = srcSheet.Range(srcSheet.Cells(rOffset + 1, _
1).Address & ":" & srcSheet.Cells(rOffset + 1, _
lastCol).Address)
'this always puts copy in Row 1 of 2nd sheet, can be changed
Set destRange = destSheet.Range("A1:" & Cells(1, _
lastCol).Address)
destSheet.Cells.Clear ' remove earlier results
destRange.Value = srcRange.Value ' transfer data
destSheet.PrintOut copies:=1
End If
rOffset = rOffset + 1
Loop
MsgBox "Job completed"
End Sub


"nc" wrote:

Hi

I cannot view your reply.


"JLatham" wrote:



"nc" wrote:

The information below would be on worksheet named €œSheet1€�
A B C
Cust id Product code Amount

1 10006 RFUUK 2000.00
2 10010 RFUUK 2000.00
3 10030 RFUOS 3000.00


I need help to write a macro to carry out the following actions,

1. I would like to have the information for each row on a specific position
(e.g. A1, A12 and C12) on another worksheet (e.g. named €œSheet2€�) then print
that page.

2. Repeat the above cycle for the each row for the list on €œSheet1€�.

Thanks. Any help appreciated.