Macro Print/Page breaks

[lbanham]

Hi,

I have a macro that someone very kindly helped me with on another thread (see below)

I need this developed a bit more to include a page break at line 70, and also to specific what sheet to apply the whole macro to, i.e. their are 60 sheets in the book, but i only want it to apply this formating to sheet 1 to sheet 50.

I am using Excel 2007

Any help would be appreciated as usual.

Lynsey



Sub test()
Dim WorkSh As Worksheet
For Each WorkSh In ActiveWorkbook.Worksheets
With WorkSh.PageSetup
.PrintArea = "$A$1:$u$190"
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.FitToPagesWide = 1
.FitToPagesTall = 2
.Zoom = False
End With
Next WorkSh
End Sub

 

[Peter_SSs]

When posting code, please post it indented. My signature block explains how. It is much harder to read/debug code that is not indented and many helpers will just pass over your post.

Setting the page to 1 page wide by 2 pages tall will over-ride any manually-set page breaks so what are you really trying to achieve?

The code below does what you ask (that is add a horizontal page break at row 70) but I suspect the result will not be what you expect. The code does, however, show how to apply page setup to the first 50 sheets.

Sub test()
    Dim i As Long
    
    For i = 1 To 50
        With Sheets(i).PageSetup
            .PrintArea = "$A$1:$U$190"
            .CenterHorizontally = True
            .CenterVertically = True
            .Orientation = xlLandscape
            .PaperSize = xlPaperA4
            .FitToPagesWide = 1
            .FitToPagesTall = 2
            .Zoom = False
        End With
        With Sheets(i)
            .ResetAllPageBreaks
            .HPageBreaks.Add .Range("A71")
        End With
    Next i
End Sub

 

[lbanham]

Thanks Peter, I will remember that for posting next time.

The code isn't doing what I need just now.

Ultimately i need it to print the specified area on to two sheets, but the page needs to break at line 70 otherwise the second sheet doesn't make sense.

Also how would i change the part that says 1 to 50, to say, Edinburgh to Glasgow? Do I simply just replace the numbers with text?

Thanks for all your help.

Regards

Lynsey

 

[Peter_SSs]

Ultimately i need it to print the specified area on to two sheets, but the page needs to break at line 70 otherwise the second sheet doesn't make sense.

With a page break after row 70, and only two sheets, that is going to require 120 rows on sheet 2. With the sheet in Landscape mode that is going to require a lot of scaling down!

Also how would i change the part that says 1 to 50, to say, Edinburgh to Glasgow? Do I simply just replace the numbers with text?

No, you cannot do that change. It you can be certain that 'Edinburgh' is the left-most sheet you want this to act on and 'Glasgow' the right-most sheet then you could use code like below. If you don't have such certainty you would have provide either ..
- a list of sheet names to act on, or
- a list of sheet names to NOT act on

To get the fit on 2 pages you will need to experiment with the Zoom factor. It will depend on your font size/row heights. With 0 top & bottom margins I needed to get the Zoom down to 40 to fit page 2.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> test()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, Lstart <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, Lend <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Lstart = Sheets("Edinburgh").Index<br>    Lend = Sheets("Glasgow").Index<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> i = Lstart <SPAN style="color:#00007F">To</SPAN> Lend<br>        <SPAN style="color:#00007F">With</SPAN> Sheets(i).PageSetup<br>            .PrintArea = "$A$1:$U$190"<br>            .CenterHorizontally = <SPAN style="color:#00007F">True</SPAN><br>            .CenterVertically = <SPAN style="color:#00007F">True</SPAN><br>            .Orientation = xlLandscape<br>            .PaperSize = xlPaperA4<br><SPAN style="color:#007F00">'            .FitToPagesWide = 1 '<-Removed</SPAN><br><SPAN style="color:#007F00">'            .FitToPagesTall = 2 '<-Removed</SPAN><br>            .Zoom = 40 <SPAN style="color:#007F00">'<- Experiment with this</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> Sheets(i)<br>            .ResetAllPageBreaks<br>            .HPageBreaks.Add .Range("A71")<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[lbanham]

Thanks for your help Peter, it's working now.

Regards