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?
--
Clark

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


On Thu, 24 Jan 2008 10:28:02 -0800, clarkb
wrote:

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?

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.
--
Clark


"Gord Dibben" wrote:

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


On Thu, 24 Jan 2008 10:28:02 -0800, clarkb
wrote:

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?

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

On Wed, 30 Jan 2008 06:00:03 -0800, clarkb
wrote:

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.