Printing specific no. of lines per page, Excel '03

  exdragon 17:43 21 Apr 09
Locked

Hi - I have data on 3552 lines which I wish to print ar 50 lines per page, with anything left over at the end.

I know I can do it via Page Break Preview and manually adjust each break, but is there an 'automatic' way of doing it as I also have two more sets of data which I need to treat in the same way.

Thanks

  VoG II 17:55 21 Apr 09

Try this. Press ALT + F11 to open the Visual Basic Editor, Insert > Module and paste in



Sub AddPB()
Dim i As Long, LR As Long
With ActiveSheet
LR = .UsedRange.Rows.Count
For i = 51 To LR Step 50
.ResetAllPageBreaks
.HPageBreaks.Add Before:=Range("A" & i)
Next i
End With
End Sub




Press ALT + F11 again to return to your sheet then Tools > Macro > Macros, highlight AddPB and click the run button.

  exdragon 18:05 21 Apr 09

VoG™ - thanks, but it's coming out at 53 lines per page. Thinking about it, I actually need 51 per page as I'd forgotten I need the first header row to be repeated. I take it I just change 50 to 51 in your macro.

  VoG II 18:08 21 Apr 09

Which row does your data start on?

  exdragon 18:49 21 Apr 09

The header row is Row 1 and I need it to be repeated on each page. I'm aiming for full pages of header + 50 rows and whatever is left over can go on the last page - doesn't matter how many rows that is, so I suppose it'll be 69 pages plus the last bit.

  VoG II 19:22 21 Apr 09

I don't think that you need to include an extra row when you are using the Rows to print at top option.

I'm mystified as when I ran the code it inserted page breaks at 50, 100, 150 and so on.

Hang on there's an error: try


Sub AddPB()
Dim i As Long, LR As Long
With ActiveSheet
.ResetAllPageBreaks
LR = .UsedRange.Rows.Count
For i = 51 To LR Step 50
.HPageBreaks.Add Before:=Range("A" & i)
Next i
End With
End Sub

  exdragon 19:30 21 Apr 09

Now it's doing the header plus to row 49...

  exdragon 19:35 21 Apr 09

And when I try it on the second set of data (separate worksheet)it has the hearder row and rows 3553 to 3601

  VoG II 19:40 21 Apr 09

I'm a bit baffled...

Try




Sub AddPB()
Dim i As Long, LR As Long
With ActiveSheet
.ResetAllPageBreaks
.PageSetup.PrintArea = .UsedRange.Address
LR = .UsedRange.Rows.Count
For i = 51 To LR Step 50
.HPageBreaks.Add Before:=Range("A" & i)
Next i
End With
End Sub

  exdragon 19:50 21 Apr 09

Still 1 to 49...

  exdragon 19:55 21 Apr 09

I have noticed that in the page set up box, the sheet tab doesn't give the option to print the header row at the top of each page, so is the macro including that row in the 50, ie, header plus 49?

This thread is now locked and can not be replied to.

Huawei P10 review

1995-2015: How technology has changed the world in 20 years

How VR is being used to simulate space

New iPad, iPhone SE & Red iPhone 7 on sale now