Announcement

Collapse
No announcement yet.

Print Multiple Sheets With VBA

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Print Multiple Sheets With VBA

    Hi,
    I have 8 sheets, each includes one page for print. I want to have a button, and when click the button, 8 sheets should be printed.
    Need your help

  • #2
    Re: Print Multiple Sheet Through Vba

    Modify the code below to suit;
    Code:
    Sub PrintSheets()
    Dim sSheet As Object
    
        For Each sSheet In Sheets
            Select Case UCase(sSheet.CodeName)
                Case "SHEET1", "SHEET2", "SHEET3", "SHEET4", _
                    "SHEET5", "SHEET6", "SHEET7", "SHEET8"
                    sSheet.PrintOut
                Case Else
                    'Whatever
            End Select
        Next sSheet
            
    
    End Sub
    Or, make use of View>Custom Views and http://www.ozgrid.com/forum/showthread.php?t=13668
    Last edited by Dave Hawley; May 29th, 2007, 14:22.

    Comment


    • #3
      Re: Print Multiple Sheet Through Vba

      Thanks for replying, I pasted the above code into a module, and assign it to object, but unfortunately not working.
      The below code works, but it flashing eight times sending each sheet to printer.
      Code:
      Sub PrintSheets()
      
         Sheet3.PrintOut
         Sheet4.PrintOut
         Sheet5.PrintOut
         Sheet6.PrintOut
         Sheet7.PrintOut
         Sheet8.PrintOut
         Sheet9.PrintOut
         Sheet10.PrintOut
            
      End Sub
      Can I do the above in a better way, and avoid from flashing?

      Comment


      • #4
        Re: Print Multiple Sheet Through Vba

        It works fine for me. Sorry, my bad I didn't use all UPPER case. Changed that now and used the CodeName.

        See stop screen flicker

        Comment


        • #5
          Re: Print Multiple Sheet Through Vba

          Thanks, yeah the problem was about the UCASE, now it works well, but when I click on the button, it flashing becaue of sending the sheets to printer, is it possible to prevent from flashing of the printering window?

          Comment


          • #6
            Re: Print Multiple Sheet Through Vba

            Did you try what I suggested?

            Comment


            • #7
              Re: Print Multiple Sheet Through Vba

              yes, it works and print the sheets, but when clicking it shows the printing window flashing, I want to just normally print them, and not flashing window appears, is it possible?

              Comment


              • #8
                Re: Print Multiple Sheets With VBA

                Log re-read post #4 above (from Dave Hawley). This contains a hyperlink to a page that describes how stop screen flickering (hover the mouse over the word "flicker")

                Ger

                Check out our new reputation system. Click on the "star" under the post!
                _______________________________________________

                There are 10 types of people in the world. Those that understand Binary and those that dont.

                Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

                The BEST Lookup function of all time

                Dynamic Named Ranges are your bestest friend

                _______________________________________________

                Comment


                • #9
                  Re: Print Multiple Sheets With VBA

                  yeah, but I can't see the data into that link.
                  means when I click the hyperlink, nothing the new window open with no data

                  Comment


                  • #10
                    Re: Print Multiple Sheets With VBA

                    No - dont click on the hyperlink.... just hover the mouse over the word... a menu of hyperlinks will pop up and I think the 1st one in the list is the one you want.

                    Ger

                    Check out our new reputation system. Click on the "star" under the post!
                    _______________________________________________

                    There are 10 types of people in the world. Those that understand Binary and those that dont.

                    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

                    The BEST Lookup function of all time

                    Dynamic Named Ranges are your bestest friend

                    _______________________________________________

                    Comment


                    • #11
                      Re: Print Multiple Sheets With VBA

                      yes, thanks much for that, I did but again when sending sheets to printer it flashing for that,
                      Code:
                       Sub PrintSheets()
                          Dim sSheet As Object
                          
                      Application.ScreenUpdating = False
                          For Each sSheet In Sheets
                                  Select Case sSheet.CodeName
                              Case "Sheet3", "Sheet4", "Sheet5", "Sheet6", _
                                  "Sheet7", "Sheet8", "Sheet9", "Sheet10"
                                  sSheet.PrintOut
                              End Select
                          Next sSheet
                      Application.ScreenUpdating = True
                                
                      End Sub

                      Comment


                      • #12
                        Re: Print Multiple Sheets With VBA

                        In case you have page numbering this will print the sheets as 1 print job.

                        Sub PrintAll()

                        Dim sSheet As Object
                        Dim blnReplace As Boolean
                        Dim shtTemp As Object

                        Set shtTemp = ActiveSheet
                        Application.ScreenUpdating = False
                        blnReplace = True
                        For Each sSheet In Sheets
                        Select Case sSheet.CodeName
                        Case "Sheet3", "Sheet4", "Sheet5", "Sheet6", _
                        "Sheet7", "Sheet8", "Sheet9", "Sheet10"
                        sSheet.Select blnReplace
                        blnReplace = False
                        End Select
                        Next sSheet
                        ActiveWorkbook.PrintOut
                        Application.ScreenUpdating = True
                        shtTemp.Select True
                        End Sub

                        Cheers
                        Andy

                        Comment


                        • #13
                          Re: Print Multiple Sheets With VBA

                          thnx for replying,
                          but It also prints the other sheets too.

                          I need:
                          1. To Print "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", "Sheet9", "Sheet10" as 1 Print Job
                          2. If possible, prevent from flashing printing window

                          Any one plz guide me through this

                          Comment


                          • #14
                            Re: Print Multiple Sheets With VBA

                            Did you try my code?

                            When I use it all I get is the dialog box telling me it's going to print 8 pages.

                            Cheers
                            Andy

                            Comment


                            • #15
                              Re: Print Multiple Sheets With VBA

                              Yeah, instead of printing those 8 sheets, it prints other sheets too.

                              Comment

                              Working...
                              X