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.