How to set print range for a number of worksheets
Simon
Try this one........no error checking.
Select all sheets then run macro.
Dim pArea As String, titleRows As String, titleCols As String
Sub Same_Titles()
titleRows = ActiveSheet.PageSetup.PrintTitleRows
titleCols = ActiveSheet.PageSetup.PrintTitleColumns
For Each oSheet In ActiveWindow.SelectedSheets
If TypeName(oSheet) = "Worksheet" Then
oSheet.PageSetup.PrintTitleColumns = titleCols
oSheet.PageSetup.PrintTitleRows = titleRows
End If
Next
End Sub
Gord
On Mon, 12 Dec 2005 02:50:04 -0800, "Simon G"
wrote:
Hi
Do you have a similar macro to allow me to set 'Rows to repeat at Top' for
many shhets in a workbook?
Thanks for your help.
"Gord Dibben" wrote:
Tom
Unfortunately setting the print area on grouped sheets is not an option
without using VBA.
Code from Bob Flanagan for setting same print area on grouped sheets.
NOTE: Set the print area on ActiveSheet then Group the sheets and run the
macro.
Note: after print area is set you will most likely have to re-group to set
margins.
Sub Set_Print_Area_On_All_Selected_Sheets()
Dim tempS As String, oSheets As Object
Dim curSheet As Worksheet, oSheet As Worksheet
Dim iResponse
Application.ScreenUpdating = False
iResponse = MsgBox(prompt:= _
"Select OK to set the print area on all " & _
"selected sheets the same as the print " & _
"area on this sheet. If you have not selected " & _
"any sheets, then all worksheets will be set.", _
Buttons:=vbOKCancel)
If iResponse = vbCancel Then End
'store info
tempS = ActiveSheet.PageSetup.PrintArea
'set an object variable to refer to the sheets to be set
If ActiveWindow.SelectedSheets.Count = 1 Then
'if no sheets selected, select all worksheets
Set oSheets = ActiveWorkbook.Worksheets
Else
'set variable to select sheets
Set oSheets = ActiveWindow.SelectedSheets
End If
'store the current sheet and then rotate through each
'sheet and set the print area
Set curSheet = ActiveSheet
For Each oSheet In oSheets
If oSheet.Type = xlWorksheet Then
'set print area only if a worksheet
oSheet.PageSetup.PrintArea = tempS
End If
Next
'return to the original worksheet
curSheet.Select
MsgBox "All print areas on the selected sheets have " & _
"been set to the same as this sheet."
End Sub
Gord Dibben Excel MVP
On Sun, 8 May 2005 20:14:02 -0700, "tntvk"
wrote:
I have a workbook with multiple (50) worksheets. I need to print only a
section for all worksheets. Is there a way I can set a print range for all
worksheets?
ANy assistance is greatly appreciated
Thank You
Tom
|