set the print area print multiple tabs
 

I am trying to designate the same print area on 70+ different tabs in the
same work book so that I can send them all to print at once as 70+ individual
pdf's. If I select more than one tab at a time, I dont have the option to
set the print area.

I think there are two issues:
1) How do I set the print area for all the tabs at the same time
2) How do I send each tab to print to Adobe PDF one at a time so that they
each become their own .pdf?

Is there another way to batch print different tabs?
Help Me Automate!! Please!

veng

The PDF stuff is out of my realm but I'll address the print range setup.

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 and Page Breaks.

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 Fri, 11 Feb 2005 09:43:05 -0800, "veng"
wrote:

I am trying to designate the same print area on 70+ different tabs in the
same work book so that I can send them all to print at once as 70+ individual
pdf's. If I select more than one tab at a time, I dont have the option to
set the print area.

I think there are two issues:
1) How do I set the print area for all the tabs at the same time
2) How do I send each tab to print to Adobe PDF one at a time so that they
each become their own .pdf?

Is there another way to batch print different tabs?
Help Me Automate!! Please!

Hi Veng & Gordon-

If I read correctly and the range to be printed is the same on each sheet,
you might want to try this...
Select the range on Sheet1 then Shift+Click the tab for Sheet70. You don't
even have to use the Set Print Area command, but if you want to, do so BEFORE
you select multiple sheets - otherwise the command will be dimmed in the menu.

Also, I think the "pdf" feature mentioned could be just a matter of Checking
the "Print to Fle" checkbox in the print dialog box. Otherwise
Acrobat/Distiller would be the best way to go.

Hope this is useful |:)
"Gord Dibben" wrote:

veng

The PDF stuff is out of my realm but I'll address the print range setup.

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 and Page Breaks.

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 Fri, 11 Feb 2005 09:43:05 -0800, "veng"
wrote:

I am trying to designate the same print area on 70+ different tabs in the
same work book so that I can send them all to print at once as 70+ individual
pdf's. If I select more than one tab at a time, I dont have the option to
set the print area.

I think there are two issues:
1) How do I set the print area for all the tabs at the same time
2) How do I send each tab to print to Adobe PDF one at a time so that they
each become their own .pdf?

Is there another way to batch print different tabs?
Help Me Automate!! Please!

Cyber

Have you actually tried your method?


Gord

On Fri, 11 Feb 2005 13:27:04 -0800, "CyberTaz"
wrote:

Hi Veng & Gordon-

If I read correctly and the range to be printed is the same on each sheet,
you might want to try this...
Select the range on Sheet1 then Shift+Click the tab for Sheet70. You don't
even have to use the Set Print Area command, but if you want to, do so BEFORE
you select multiple sheets - otherwise the command will be dimmed in the menu.

Also, I think the "pdf" feature mentioned could be just a matter of Checking
the "Print to Fle" checkbox in the print dialog box. Otherwise
Acrobat/Distiller would be the best way to go.

Hope this is useful |:)
"Gord Dibben" wrote:

veng

The PDF stuff is out of my realm but I'll address the print range setup.

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 and Page Breaks.

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 Fri, 11 Feb 2005 09:43:05 -0800, "veng"
wrote:

I am trying to designate the same print area on 70+ different tabs in the
same work book so that I can send them all to print at once as 70+ individual
pdf's. If I select more than one tab at a time, I dont have the option to
set the print area.

I think there are two issues:
1) How do I set the print area for all the tabs at the same time
2) How do I send each tab to print to Adobe PDF one at a time so that they
each become their own .pdf?

Is there another way to batch print different tabs?
Help Me Automate!! Please!

hi!
thanks so much for responding. one more question- what is VBA?


"Gord Dibben" wrote:

veng

The PDF stuff is out of my realm but I'll address the print range setup.

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 and Page Breaks.

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 Fri, 11 Feb 2005 09:43:05 -0800, "veng"
wrote:

I am trying to designate the same print area on 70+ different tabs in the
same work book so that I can send them all to print at once as 70+ individual
pdf's. If I select more than one tab at a time, I dont have the option to
set the print area.

I think there are two issues:
1) How do I set the print area for all the tabs at the same time
2) How do I send each tab to print to Adobe PDF one at a time so that they
each become their own .pdf?

Is there another way to batch print different tabs?
Help Me Automate!! Please!

Visual Basic for Applications

See help for more. Type vba into Answer Wizard.

Also see David McRitchie's "getting started with VBA and macros"

http://www.mvps.org/dmcritchie/excel/getstarted.htm


Gord

On Sat, 12 Feb 2005 08:41:03 -0800, "veng"
wrote:

hi!
thanks so much for responding. one more question- what is VBA?


"Gord Dibben" wrote:

veng

The PDF stuff is out of my realm but I'll address the print range setup.

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 and Page Breaks.

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 Fri, 11 Feb 2005 09:43:05 -0800, "veng"
wrote:

I am trying to designate the same print area on 70+ different tabs in the
same work book so that I can send them all to print at once as 70+ individual
pdf's. If I select more than one tab at a time, I dont have the option to
set the print area.

I think there are two issues:
1) How do I set the print area for all the tabs at the same time
2) How do I send each tab to print to Adobe PDF one at a time so that they
each become their own .pdf?

Is there another way to batch print different tabs?
Help Me Automate!! Please!