Print range for excel - multiple pages, variable rows

[haven104]

I'm not a programmer, but I don't want to sit down and do this to 400 pages.

My workbook contains a variable number of pages. Each page contains the same column headings, but the number of rows is variable. Each row contains a scientific reading recording time, date, temp, etc. What I need is to print the full range from A1:K(last filled row), and exclude any data columns past K.
Then I need to apply this to every worksheet in the workbook.

Sub printmacro()

'   With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$4"
        .PrintTitleColumns = "$A:$K"
    End With

    ActiveSheet.PageSetup.PrintArea = "$A$1:$K$77"
    
End Sub

 

[ATylman]

Hi haven104

The following macro will print all your sheets to column K using row 1 as the row to repeat at the top.

Sub Printrange_All_Sheets()

'declares variables for counting rows, active sheet number and total sheets
Dim sheetscount As Integer
Dim sheetnumber As Integer
Dim rw As Integer

'sets variables for counting rows, active sheet number and total sheets
rw = 1
sheetnumber = 1
sheetscount = Worksheets.Count

'starts at first sheet
Worksheets(1).Activate

'cycles through all sheets
Do While sheetnumber <= sheetscount

    'counts number of rows on sheet
    Worksheets(sheetnumber).Activate
        Do Until Cells(rw, 1) = &quot;&quot;
            rw = rw + 1
        Loop
        
        'sets title row
        With ActiveSheet.PageSetup
        .PrintTitleRows = &quot;$1:$1&quot;
        .PrintTitleColumns = &quot;&quot;
        End With
        
        'prevents error if sheet is blank
        If rw = 1 Then rw = 2
            
        'prints sheets
        Range(&quot;A1:K&quot; & rw - 1).Select
        Selection.PrintOut Copies:=1, Collate:=True
           
 'moves sheets up 1 and resets row variable
 sheetnumber = sheetnumber + 1
 rw = 1
 Loop
    
End Sub

 

[combo]

Another macro option:

[tt]Sub PrintAllPages()
Dim sh As Worksheet
With ThisWorkbook
For Each sh In .Worksheets
With sh.PageSetup
.PrintArea = "$A:$K"
.PrintTitleRows = "$1:$4"
.Zoom = False
.FitToPagesTall = False
.FitToPagesWide = 1
End With
sh.PrintOut
Next sh
End With
End Sub[/tt]

combo

 

[SkipVought]

HI,

If it's just that you want to have this applied to ANY sheet that you print, then this could be done with the Workbook_Print event, modifying Combo's code...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    With ActiveSheet.PageSetup
        .PrintArea = "$A:$K"
        .PrintTitleRows = "$1:$4"
        .Zoom = False
        .FitToPagesTall = False
        .FitToPagesWide = 1
    End With
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

I meant to say the Worksheet_BeforePrint event.

Sorry

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com