How do I set global in print titles?

[clarkb]

Every time I print multiple pages I have to go to Page Setup, select Sheet
and then put $1:$1 in the Print titles box. Is there a way to set that
globally so all I have to do is change it if I need a different print area?

 

[Gord Dibben]

Clark

You could run a small macro to set the print titles for all sheets in a
workbook.

Sub Print_Row()
For Each oSheet In oSheets
If oSheet.Type = xlWorksheet Then
oSheet.PageSetup.PrintTitleRows = "$1:$1"
End If
Next
End Sub

Or for just one sheet, which could be placed in a Workbook_BeforePrint event in
Thisworkbook

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim oSheet As Worksheet
Set oSheet = ActiveSheet
With oSheet
If oSheet.Type = xlWorksheet Then
oSheet.PageSetup.PrintTitleRows = "$1:$1"
End If
End With
End Sub

As an alternative to VBA you could just create a workbook Template with your
seetings that would become the default New workbook for Excel.

See this thread for more info on that.

http://tinyurl.com/3x4ety


Gord Dibben MS Excel MVP

 

[clarkb]

Thanks, Gord. I did what you said in 'http://tinyurl.com/3x4ety'
and it works great for opening new sheets, however, the problem I was having
was exporting data from Access to Excel using the Print Preview then Analyze
with Excel feature. I do this a lot and I always want the pages to be
landscape and print titles to repeat on each page. The fix didn't work
because Access directly inserts the data into a sheet/book and not into
'sheet.xlt'. Any suggestions would be appreciated.

 

[Gord Dibben]

Don't bother with the Template if Access creates its own workbook and sheet
from Excel's default.

After the workbook and sheet(s) have been created by Access, run this macro.

Sub Print_Row()
Dim oSheet As Worksheet
For Each oSheet In ActiveWorkbook.Sheets
If oSheet.Type = xlWorksheet Then
With oSheet.PageSetup
.PrintTitleRows = "$1:$1"
.Orientation = xlLandscape
End With
End If
Next
End Sub

Store it in your Personal.xls so's it is available for all open workbooks.


Gord