Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 17

Thread: Print Multiple Sheets With VBA

  1. #1
    Join Date
    3rd May 2007
    Posts
    191

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,697

    Re: Print Multiple Sheet Through Vba

    Modify the code below to suit;
    VB:
    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 at 14:22.

  3. #3
    Join Date
    3rd May 2007
    Posts
    191

    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.
    VB:
    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?

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,697

    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

  5. #5
    Join Date
    3rd May 2007
    Posts
    191

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,697

  7. #7
    Join Date
    3rd May 2007
    Posts
    191

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,411

    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
    _______________________________________________
    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

    _______________________________________________

  9. #9
    Join Date
    3rd May 2007
    Posts
    191

    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

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,411

    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
    _______________________________________________
    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

    _______________________________________________

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 1
    Last Post: January 28th, 2009, 03:11
  2. Print Multiple Sheets To Pdf File
    By praveen_ in forum EXCEL HELP
    Replies: 9
    Last Post: June 14th, 2008, 12:06
  3. Replies: 6
    Last Post: June 23rd, 2007, 04:20
  4. Vba: Set Print Format Across Multiple Sheets
    By dcrose in forum EXCEL HELP
    Replies: 2
    Last Post: June 15th, 2007, 22:01
  5. Printing Multiple Sheets as 1 Print Job
    By Razzcul in forum EXCEL HELP
    Replies: 1
    Last Post: August 25th, 2006, 14:52

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
  •  
porno