Macro for Multiple Form Creation
 

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 Sub
-----------------------------------------------------------------------------------

Any help gratefully recieved. Please do not hesitiate to contact me if you
require any further info.


cheers


jb

Macro for Multiple Form Creation
 

Hi


Sorry!


Ive fixed it

You remove the the last line about PrintOut & change Set Rng to Active Cell

Sorry for wasting everyone's time


jb

"JB2010" wrote:

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 Sub
-----------------------------------------------------------------------------------

Any help gratefully recieved. Please do not hesitiate to contact me if you
require any further info.


cheers


jb