macro - page breaks/printing

[deborahleah]

is there a macro code that will autmatically determine the end of your page and adjust your page breaks accordingly?

 

[Mark F]

Hi

Not sure exactly what you mean on this one. Could you perhaps explain a bit more

Mark

 

[deborahleah]

I have to adjust the page breaks on every worksheet in a 50tab file. Each tab has various rows of data (never identical). Can i just run a macro to set the page break (to last row with data) on each tab, repeat header, and extend out to column Z and no further? Does that make sense?

 

[Mark F]

A couple of questions

1) Do you want each tab to print on a single page, or will the data per tab go onto more than one printed sheet ?

2) When you say repeat header do you mean repeat the top row onto every printed sheet (if the tab data goes onto more than one sheet), or the "Header" as in View>Header and Footer

3)Does your data on each sheet go further than Column Z

Mark

 

[hero85]

this is a good thread. I am keenly waiting for a solution to this problem.

 

[VoG]

Not entirely clear. Try this: right click the Excel logo just to the left of File on the menu bar, select View Code and paste in

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet, LR As Long
For Each ws In ActiveWindow.SelectedSheets
    LR = ws.Range("A" & Rows.Count).End(xlUp).Row
    ws.PageSetup.PrintArea = ws.Range("A1:Z" & LR).Address
Next ws
End Sub

 

[deborahleah]

very close! the only problem is that i want columns a-z on each page lanscape layout. i do not want any page breaks from a to z, all columns must show on each printed sheet. but there can be countless page breaks in the rows. does that make sense?

thank you!

 

[deborahleah]

A couple of questions

1) Do you want each tab to print on a single page, or will the data per tab go onto more than one printed sheet ? data per tab can onto more than one printed sheet but i do not want columns on multiple sheets, columns a-z must print on each sheet lanscape layout.

2) When you say repeat header do you mean repeat the top row onto every printed sheet (if the tab data goes onto more than one sheet), or the "Header" as in View>Header and Footer- repeat rows 1-8 on every printed sheet

3)Does your data on each sheet go further than Column Z - no it does not. the rows of data always vary but the columns are static.

 

[Mark F]

Sub RowPageSetUp()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        LR = ws.Range("A" & Rows.Count).End(xlUp).Row
        ws.PageSetUp.PrintTitleRows = "$1:$8"
        ws.PageSetUp.PrintArea = ws.Range("A1:Z" & LR).Address
        With ws.PageSetUp
            .Orientation = xlLandscape
            .FitToPagesWide = 1
            .FitToPagesTall = 500    ' or  whatever?
        End With
        On Error Resume Next
    Next ws
End Sub

 

[deborahleah]

perfect!!! thank you!