Excel Macros are driving me mad

[higherstate]

I have a spreadsheet that will be of variable amount of rows but with fixed headers i.e.

address
address

header header header
data data data
data data data
data data data

What I want to be able to do is print the different rows of data on seperate pages with the address & headers included on each page.

As the data will be of variable length it also needs to stop printing when it comes across an empty cell.

My skills are obviously not up to scratch as it is alot harder than I thought it would be

Any help you can give would be much appreciated.

 

[Andrew Poulsom]

Welcome to the Board.

Say your data is in the range A1:C7. Choose File|Page Setup and on the Sheet tab set "Rows to repeat at top" to $1:$4 and click OK. Select A6 and choose Insert|Page Break. Repeat for A7. Then print your worksheet (you can preview if you want).

 

[higherstate]

Hi Andrew, thanks for the reply.

If only it were that simple

The issue I have found when recording the macro is that excel ignores page breaks that are next to each other and defaults to printing on one page.

Also, how can I tell it to stop when it it comes across an empty cell?

 

[Andrew Poulsom]

Try this:

Sub Test()
    Dim Sh As Worksheet
    Dim LastCell As Long
    Dim r As Long
    Set Sh = Worksheets("Sheet1")
    With Sh
        .PageSetup.PrintTitleRows = "$1:$4"
        LastCell = .Cells(.Rows.Count, 1).End(xlUp).Row
        For r = 6 To LastCell
            .HPageBreaks.Add Before:=.Cells(r, 1)
        Next r
        .PrintPreview
    End With
End Sub

Change the sheet reference if necessary.

 

[higherstate]

Okay thanks Andrew, with a bit of tweaking I got it to work how I needed it to, looks like I need to learn visual basic

My next challange is todo the same thing in open office!