How to set print range for a number of worksheets
 

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

Hi Tom
Yes you can set a print area for multiple worksheets:
Right-click on any sheet tab and choose "Select all sheets" (please note
this option may vary since i am using a french version of Excel)
If you do not want to set this for all of your sheets, press <Ctrl and
select each sheet tab you wish to add and release <Ctrl when you are done.
Define your print area, it wil be set for the selected sheets.

HTH
Cordially
Pascal

"tntvk" a écrit dans le message de news:
...
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

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

How to set print range for a number of worksheets
 

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

How to set print range for a number of worksheets
 

You could change this portion:

For Each oSheet In oSheets
If oSheet.Type = xlWorksheet Then
'set print area only if a worksheet
oSheet.PageSetup.PrintArea = tempS
End If
Next

to

For Each oSheet In oSheets
If oSheet.Type = xlWorksheet Then
oSheet.PageSetup.PrintTitleRows = "$1:$2"
End If
Next

Change $1:$2 to what you want.


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



--

Dave Peterson

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