Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    Glasgow, Lanarkshire, Scotland
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Printing Selections on multiple w/sheets

    Hi All,

    Got a problem with printing a range (the same range) over multiple worksheets within a workbook.

    I found the nifty feature on the print dialogue box (what a relief!) and decided to put it into a macro.

    The code I recorded is:

    ---------------------------
    Sub PrintSS01()
    Sheets(Array("Wmns", "ft", "ms", "di", "st", "tb", "fi", "fc", "hl", "bx", "dk", "gi", "pg")).Select
    Sheets("Wmns").Activate
    Range("A1:AM54").Select
    Selection.PrintOut Copies:=1, Collate:=True
    End Sub
    ---------------------------

    OK - so its not the mnost taxing of code - but for some reason it only prints the first sheets (in this case "Wmns). I guess this is because of the line "Sheets("Wmns").Activate" - but you've got to activate something to select the range.

    Can anyone help please.


    tia

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Printing Selections on multiple w/sheets

    Hi,
    Try changing the last line to:
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    and see if that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Lounger
    Join Date
    Apr 2001
    Location
    Glasgow, Lanarkshire, Scotland
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing Selections on multiple w/sheets

    Hi,

    This does not seem to do what I want it to!

    What it does do is print all pages of all worksheets, rather than the range on each worksheet.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Printing Selections on multiple w/sheets

    Hi Andrew,
    Sorry about that - my quick test looked OK, but that was because the selected area was also the only data on the sheets! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Something like this could be adapted for your situation though I hope - it loops through all the sheets in the workbook, sets the printarea, prints it and then clears the printarea:
    Sub PrintSheetRanges()
    Dim sht As Excel.Worksheet
    For Each sht In ActiveWorkbook.Sheets
    With sht
    .PageSetup.PrintArea = Selection.Address
    .PrintOut
    .PageSetup.PrintArea = ""
    End With
    Next
    End Sub
    Is that of any use?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Printing Selections on multiple w/sheets

    Pilfered!
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing Selections on multiple w/sheets

    If you have the print range set in each sheet, then it's just a case of doing a mulitiple selection of these sheets & print! No need for any code at all.

  7. #7
    Lounger
    Join Date
    Apr 2001
    Location
    Glasgow, Lanarkshire, Scotland
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing Selections on multiple w/sheets

    Rory,

    Thanks for that. It works well.

    I am keen to lear why my first attempt at the code did not work. Logically it 'appeared' to do what it was supposed to.

    Any clues?

    tia,

    Andrew

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing Selections on multiple w/sheets

    Hi Andrew,

    I can give this a shot (trying to explain).

    There are several ways to print a range using VBA.

    1) print the preset printarea of the active sheet:

    Activesheet.Printout

    2) print the current selection (can be a range of cells as in your code, or an object, such as a chart)

    Selection.Printout

    3) print the set printarea of another sheet:

    Worksheets("TheOtherSheetsName").printout

    4) Print some other area of a certain sheet

    Worksheets("TheOtherSheetsName").Range("A1:B6").Pr intOut

    5) Print the preset printarea's of a series of sheets:

    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Printout

    Clear as mud?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Printing Selections on multiple w/sheets

    True dat. However, if you always select the same 18 sheets, or whatever, why not create macro and attach it to a button?
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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