J
JB2010
Hi
I have this macro that takes data from a worksheet & fills in some cells on
a form (on another worksheet). As you can see, when you press Go it fills in
the cells from the first row of the master sheet, runs it straight to the
printer, goes back fills in the sheet with the next row's data, prints, etc,
etc.
I was wondering if anyone could suggest an amendment to the script that
still fills in the cells on the form one by one, but allows me to view each
different version the form (from each row of the master sheet) one at a time
(i suppose i would need to insert a "Next" button on the form). I could then
manually choose which ones i wanted to print & which i didnt. I could also
copy a soft version of the data before i printed it as hard copy.
Here is the code:
------------------------------------------------------------------------------------
Sub TSTest()
'TSTest Macro
' Macro recorded 20/03/2006 by beltonj
Dim rng As Range
With Worksheets("Master")
Set rng = .Range(.Cells(3, 1), .Cells(1, 1).End(xlDown))
End With
For Each cell In rng
Worksheets("Form1").Range("C3").Value = cell.Offset(0, 12).Value
Worksheets("Form1").Range("C4").Value = cell.Offset(0, 5).Value
Worksheets("Form1").Range("C5").Value = cell.Offset(0, 4).Value
Worksheets("Form1").Range("C6").Value = cell.Offset(0, 3).Value
Worksheets("Form1").Range("C7").Value = cell.Offset(0, 2).Value
Worksheets("Form1").Range("C8").Value = cell.Value
Worksheets("Form1").Range("C9").Value = cell.Offset(0, 1).Value
Worksheets("Form1").Range("C10").Value = cell.Offset(0, 6).Value
Worksheets("Form1").Range("C11").Value = cell.Offset(0, 9).Value
Worksheets("Form1").Range("C12").Value = cell.Offset(0, 7).Value
Worksheets("Form1").Range("C13").Value = cell.Offset(0, 8).Value
Worksheets("Form1").Range("C14").Value = cell.Offset(0, 11).Value
Worksheets("Form1").Range("C15").Value = cell.Offset(0, 14).Value
Worksheets("Form1").PrintOut
Next
End Su
-----------------------------------------------------------------------------------
Any help gratefully recieved. Please do not hesitiate to contact me if you
require any further info.
cheers
jb
I have this macro that takes data from a worksheet & fills in some cells on
a form (on another worksheet). As you can see, when you press Go it fills in
the cells from the first row of the master sheet, runs it straight to the
printer, goes back fills in the sheet with the next row's data, prints, etc,
etc.
I was wondering if anyone could suggest an amendment to the script that
still fills in the cells on the form one by one, but allows me to view each
different version the form (from each row of the master sheet) one at a time
(i suppose i would need to insert a "Next" button on the form). I could then
manually choose which ones i wanted to print & which i didnt. I could also
copy a soft version of the data before i printed it as hard copy.
Here is the code:
------------------------------------------------------------------------------------
Sub TSTest()
'TSTest Macro
' Macro recorded 20/03/2006 by beltonj
Dim rng As Range
With Worksheets("Master")
Set rng = .Range(.Cells(3, 1), .Cells(1, 1).End(xlDown))
End With
For Each cell In rng
Worksheets("Form1").Range("C3").Value = cell.Offset(0, 12).Value
Worksheets("Form1").Range("C4").Value = cell.Offset(0, 5).Value
Worksheets("Form1").Range("C5").Value = cell.Offset(0, 4).Value
Worksheets("Form1").Range("C6").Value = cell.Offset(0, 3).Value
Worksheets("Form1").Range("C7").Value = cell.Offset(0, 2).Value
Worksheets("Form1").Range("C8").Value = cell.Value
Worksheets("Form1").Range("C9").Value = cell.Offset(0, 1).Value
Worksheets("Form1").Range("C10").Value = cell.Offset(0, 6).Value
Worksheets("Form1").Range("C11").Value = cell.Offset(0, 9).Value
Worksheets("Form1").Range("C12").Value = cell.Offset(0, 7).Value
Worksheets("Form1").Range("C13").Value = cell.Offset(0, 8).Value
Worksheets("Form1").Range("C14").Value = cell.Offset(0, 11).Value
Worksheets("Form1").Range("C15").Value = cell.Offset(0, 14).Value
Worksheets("Form1").PrintOut
Next
End Su
-----------------------------------------------------------------------------------
Any help gratefully recieved. Please do not hesitiate to contact me if you
require any further info.
cheers
jb