VBA to Insert Page Breaks every nth row, getting info from cell value

[scottclayton]

Greetings Excelers

I appear to be stuck on inserting page rows every nth line by getting that value from a cell value on each sheet.

With each sheet, cell BA2 is going to have a number in it, for example, 57, which is the number of rows to print on a page. Cell BA3 will have a value of the furthest-down row to print—e.g. 4218.

I'm trying to set it that for each worksheet selected, the sheet will be .FitToPagesWide = 1, and .FitToPagesTall = False, with page breaks after each BA2 rows. In case we need to know the number of rows down the last printable info is, BA3 has that value—end(xldown) won't work for this.

I'm automating a label-maker that has sheets with varying label sizes, and sometimes I might print a half-dozen sheets at once, but printers often have different margins, and different margins depending on the computer talking to it, which is annoying.

Anyone have any ideas?

Thanks!


-Scott

 

[par60056]

Welcome to the forum. This is not very difficult. I have done it several times.

Replying now so that I can find the thread tomorrow when I am at work and have access to my code.

it will look something like :

Dim currRow As Long
Dim theSht As Worksheet


For Each theSht In ActiveWorkbook.Sheets
    currRow = theSht.Cells(2, 53)
    While currRow < theSht.Cells(3, 53)
        theSht.Rows(currRow).PageBreak = xlPageBreakManual
        currRow = currRow + theSht.Cells(2, 53)
    Wend
Next

 

[scottclayton]

par60056,

I think you've essentially got it!

Thank you very much.

 

[par60056]

Good. Thanks for the feedback. There are a few things that you will probably want to add.

Disable screen updating and recalculation. It will speed things up.

You can also set the page width in there for each sheet using the PageSetup object.