+ Reply to Thread
Results 1 to 6 of 6

set the print area print multiple tabs

  1. #1
    veng
    Guest

    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!

  2. #2
    Gord Dibben
    Guest

    Re: set the print area print multiple tabs

    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" <[email protected]>
    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!



  3. #3
    CyberTaz
    Guest

    Re: set the print area print multiple tabs

    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" <[email protected]>
    > 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!

    >
    >


  4. #4
    Gord Dibben
    Guest

    Re: set the print area print multiple tabs

    Cyber

    Have you actually tried your method?


    Gord

    On Fri, 11 Feb 2005 13:27:04 -0800, "CyberTaz"
    <[email protected]> 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" <[email protected]>
    >> 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!

    >>
    >>



  5. #5
    veng
    Guest

    Re: set the print area print multiple tabs

    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" <[email protected]>
    > 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!

    >
    >


  6. #6
    Gord Dibben
    Guest

    Re: set the print area print multiple tabs

    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" <[email protected]>
    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" <[email protected]>
    >> 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!

    >>
    >>



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1