PDA

View Full Version : Solved: Print Data into Form



parttime_guy
08-25-2011, 10:13 AM
Hi Guz-n-Galz,

Picture This - Data in Rows (Sheet1) - Format/Form (Sheet2)

What would be the best approach to print all data that are in rows (Sheet1) into the Format/Form (Sheet2) - one after another till all existing rows are complete.

Anything would be acceptable : pray2: :help - views/ideas/formats/links.

Thx-n-BR

parttime_guy
08-30-2011, 11:11 AM
Hi Everybody,

After searching the net and mixing code together I have tried to solve the printing Form query, but I have hit another :banghead:

The code works fine if the header is in the first row and the data follows.

But...

How do I change the code if the heading is in row 6 data starts from row 7 ???

Can anybody help me to amend the code below.

Public Sub ProcessData()
Dim LastRow As Long
Dim i As Long
With Worksheets("Master")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow
Sheets("Summary").Select
Worksheets("Summary").PrintOut
.Rows(2).Copy .Rows(LastRow + 1)
.Rows(3).Resize(LastRow - 2).Copy .Range("A2")
.Rows(LastRow).Delete
Next i
End With
MsgBox "Finished Processing"
End Sub


Thx-n-BR

Aussiebear
08-30-2011, 03:17 PM
Try changing the line
For i = 2 to LastRow
to
For i = 7 to LastRow
and see what happens

parttime_guy
08-30-2011, 07:40 PM
I tried doing that...

The code runs ok for the first time

But...:bug:

The information in the Master flap automatically changes - and goes for a toss.

Kindly view file.

Thx-n-BR

Kenneth Hobs
08-31-2011, 05:27 AM
I don't have time to test this right now.

Public Sub ProcessData()
Dim LastRow As Long, startDataRow As Long
Dim i As Long

With Worksheets("Master")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
startDataRow = Range("A" & Rows.Count).End(xlUp).Row
startDataRow = Range("A" & startDataRow).End(xlUp).Row - 1
If startDataRow > LastRow Then Exit Sub

For i = startDataRow To LastRow

Sheets("Summary").Select

Worksheets("Summary").PrintOut
.Rows(startDataRow).Copy .Rows(LastRow + 1)
.Rows(startDataRow + 1).Resize(LastRow - startDataRow).Copy .Range("A" & startDataRow)
.Rows(LastRow).Delete
Next i
End With

MsgBox "Finished Processing"

End Sub

A better approach might be to use the method that John Walkenbach used in his ElephantsRus. He used Indirect() to fill the form cells based on a row cell. To print, you then just iterate the rows from the start row to the end row.

parttime_guy
08-31-2011, 06:59 PM
Thx Kenneth for ur reply - it works like a charm (...except there are 2 extra pages that get printed - but I'll live with that).

But the data in in the Master file remains intact that's what really matters.

I have downloaded John Walkenbach "ElephantsRus" will take a look at it.

:bow: :friends:

Best regards