Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Apr 2001
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multi-sheet report from Excel (2000)

    Before I stumble around re-inventing the wheel, has anyone a VBA method for printing multiple sheets from a workbook? I'll have a form that allows users to select which sheets they want, and the selections will be stored in the book as booleans. When they hit my print button, the code should select the sheets based on the stored booleans. Each user may want to print different sets of sheets.
    I've had no luck creating a string of sheet names (sSheetsToPrint) that is then used by Sheets(Array(sSheetsToPrint)).Select. I was hoping to use this as the pages then get numbered correctly.
    Thanks in advance,

  2. #2
    New Lounger
    Join Date
    Apr 2001
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-sheet report from Excel (2000)

    Have found solution by reading other posts and scouring the Excel sites noted elsewhere. If any are interested I'll post it.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Multi-sheet report from Excel (2000)

    Yes, David, I'm interested. Also, I am interested in resolving the following problem: I have a workbook consisting of several (>1) worksheets. Each worksheet is made up of multiple pages. The problem is that, for example, page 1 on worksheet 1 should be printed in landscape mode, page 2 on the same worksheet, in portrait mode, page 3 of the same worksheet in landscape mode, etc. Without resorting to VBA, I have been unable to figure out how to do this. Any suggestions?

  4. #4
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Quakertown, PA, Pennsylvania, USA
    Posts
    517
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-sheet report from Excel (2000)

    Multi-layouts:

    You should be able to record a macro to do this for you. Let us know if you need help recording a macro.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Multi-sheet report from Excel (2000)

    Yes I do. I know absolutely nothing about VBA. Will the macro be able to recognize the lifferent layouts (i. e., portrait vs. landscape) on each worksheet?
    Thanks,
    Jeff

  6. #6
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Quakertown, PA, Pennsylvania, USA
    Posts
    517
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-sheet report from Excel (2000)

    Only because when you record the macro, you'll have to take the exact manual steps you would have to take to do this.

    So, Tools-Macro-Record a new macro. Give it a name, save it in This workbook, and hit ok.

    Steps to take, which you'll know better than me:

    1. Select the area you want to print, and hit File-Set Print area.
    2. Do File-Page setup and change the orientation, percentage/fit-to, etc. Do a print preview if you want (or you can just double-check all the steps you have to take before you record the macro so you don't have to preview now).
    3. At this point, you may want to print it, and then move to your next "layout" OR you may want to stop the macro and just call this macro "XxxLayout", and make one macro for each layout.

    Also, try using Custom Views to set up the view you want to see/print. You may want to create a custom view for each. Then, while recording your macro, you can switch to that custom view and then move on to your layout/print options.

    If I've completely lost you, yell!

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Multi-sheet report from Excel (2000)

    Dreamboat,
    If I do this, and I later change the layout, will the macro recognize and correctly print the changed layouts?
    Thanks,
    Jeff

  8. #8
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-sheet report from Excel (2000)

    No it won't. That's one of the problems with relying on recorded macros as opposd to writing your own intelligent ones with VBA. Macros merely store your keyboard movements and turns them into a VBA routine which you can then run later. The VBA that it uses isn't very good either but it's intended for quick, repetitive tasks not anything that requires logic.

    Deb <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Multi-sheet report from Excel (2000)

    De,
    Sounds like you know how to do it as well-any hints?
    Jeff

  10. #10
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-sheet report from Excel (2000)

    Yes, but in post 78664, the person said they didn't want VBA and that's the only solution. Plus another post said they found a fix so I didn't bother to respond anymore. I had written code for my own project to print a subset of sheets from my workbook with fixed footer and orientation. I have naming conventions for my worksheets so I know what "type" they are based on that convention and have code that does certain things with them. It makes it easy to add sheets and not change code if I stick with this convention. Here is code that prints worksheets that start with "e." as their name (in my case that means something special but you can change it as needed of course to pick out the appropriate sheets). The code goes through all the worksheets looking for sheets that match a certain criteria, then it stores the sheet names in an array and prompts the user to make sure they want to print, then it sets some attributes and prints the sheets.
    <pre>'------------------------------------------------------------------
    ' SummaryPrint() - Print the executive summary sheets. Optionally,
    ' the sheets can be written to a file too.
    '------------------------------------------------------------------
    Public Sub SummaryPrint(Optional toFile As Boolean = False)
    Dim sumSheets() As String, k As Integer, sht As Worksheet
    Dim msg As String

    'how many summary sheets?
    k = 0
    ReDim sumSheets(1 To 10)
    For Each sht In ThisWorkbook.Worksheets
    If InStr(sht.Name, "e.") = 1 Then
    k = k + 1
    sumSheets(k) = sht.Name
    End If
    Next sht

    If k > 0 Then 'found at least one worksheet to print...
    ReDim Preserve sumSheets(1 To k)
    msg = vbCrLf
    For k = LBound(sumSheets) To UBound(sumSheets)
    msg = msg & vbCrLf & SheetTitle(sumSheets(k))
    Next k

    If MsgBox("The following worksheets will be printed." & msg, vbExclamation + vbOKCancel, _
    "Continue Print Job?") = vbCancel Then Exit Sub

    Application.ScreenUpdating = False

    For k = LBound(sumSheets) To UBound(sumSheets)
    Set sht = ThisWorkbook.Worksheets(sumSheets(k))
    ' sht.PrintOut preview:=False, PrintToFile:=toFile 'PrintPreview

    sht.PageSetup.CenterFooter = "Carrier Services Model (" & Date & "), Cisco Systems, Inc."
    sht.PageSetup.Orientation = xlLandscape
    sht.PrintOut PrintToFile:=toFile 'prints but not to file, why?
    Next k

    Application.ScreenUpdating = True

    Else
    MsgBox "There are no summary sheets to print.", vbExclamation + vbOKOnly, "Print Error"
    End If

    End Sub 'SummaryPrint()
    </pre>


    The function SheetTitle() is my own and it just looks at the given title for a worksheet which in my case is the text shown in column A for each sheet and is not the same as the name on the sheet tab (although it could be). In the code I just added a simple footer, set the page orientation (which can be something else of course) and provided an optional flag to print to a file but for some reason that didn't work. I've run this in xl97 and xl2000.

    HTH, Deb <img src=/S/lightbulb.gif border=0 alt=lightbulb width=15 height=15>

Posting Permissions

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