Print Area

M

Mandeep Dhami

Hi,

I have a data which have a common header row. There are many rows following
the header row. (for example header row is A1:E1)
The data will be in rows 2 onwards.
I want a print out of each row along with header row one below the other so
that after taking the print on A4 size paper make a small chit of same and
distribute to others. Just to avoide wasting papers. The data rows will
hardly fill 1/4 of a page.
The print out should have rows A1:E1 & A2:E2 then A1:E1 & A3:E3 then A1:E1 &
A4:E4 and so on.
Hope I am able to clear what I want.

Cheers,
Mandeep
 
M

Mandeep Dhami

Anne Thanks for the reply.
But sorry to say the solution provided is not helpful to me. It seems the
same will be helpful if one needs to prepare letters.
 
D

Dave Peterson

Do you mean you want each line printed on its own sheet (along with row 1)?

Or you want a bunch of "couplets" printed on one sheet. Then you'll use
scissors to cut each pair of lines out and distribute those little pieces of
paper to others?

I'm guessing the second version....

This also puts a gap between each couplet--to make cutting a bit easier.

Option Explicit
Sub testme()

Dim newWks As Worksheet
Dim curWks As Worksheet
Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long

Set curWks = Worksheets("sheet1")

'make a copy of it
curWks.Copy _
after:=curWks

Set newWks = ActiveSheet

With newWks
With .UsedRange
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
.Rows(1).Insert
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow + 2 Step -1
.Range("a1").Resize(2).EntireRow.Copy
.Rows(iRow).Insert
Next iRow
.Rows(1).Delete
End With

End Sub

Do a print preview to see where the page breaks. You'll have to adjust it so
that it doesn't chop a couple into pieces.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top