Macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
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.
Click to expand...
 
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
 
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 said:
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 said:
Hi

I cannot view your reply.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
Hi

I cannot view your reply.


:



:

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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top