Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Printing multiple worksheets using macros

  1. #1
    Join Date
    12th April 2005
    Location
    Ahmedabad, India
    Posts
    29

    Printing multiple worksheets using macros

    Suppose there are 3 worksheets in a workbook and I want to print all the 3 worksheets using macro. I was able to write the macro, but it is displayng the worksheet on the screen while proceeding for printing. What I want is the the sheets being printed should not be displayed on the screen while printing.

    Thanks in advance

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,229

    Re: Printing multiple worksheets using macros

    Put the following line at the start of your macro before the proint lines
    VB:
    Application.ScreenUpdating = False 
    
    
    then at the end after printing put the same line but change the False to True.
    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

  3. #3
    Join Date
    12th April 2005
    Location
    Ahmedabad, India
    Posts
    29

    Re: Printing multiple worksheets using macros

    Thanks Derk. I tried but it is not working. Below is the code for which I want the help. You can see that I have added the said statements before and after the print command but still it is displaying while printing.

    Private Sub CommandButton6_Click()
    ActiveWorkbook.Unprotect ("R")
    Sheets("Local").Visible = True
    Sheets("Local").Select
    Application.Run "RT_Stock.xls!Macro2Local"
    Application.ScreenUpdating = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Application.ScreenUpdating = True
    Application.Run "RT_Stock.xls!Macro3Local"
    Sheets("Local").Visible = False
    Sheets("NewGen").Visible = True
    Sheets("NewGen").Select
    Application.Run "RT_Stock.xls!Macro2NewGen"
    Application.ScreenUpdating = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Application.ScreenUpdating = True
    Application.Run "RT_Stock.xls!Macro3NewGen"
    Sheets("NewGen").Visible = False
    Sheets("Sp_Pkg").Visible = True
    Sheets("Sp_Pkg").Select
    Application.Run "RT_Stock.xls!Macro2Sp_pkg"
    Application.ScreenUpdating = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Application.ScreenUpdating = True
    Application.Run "RT_Stock.xls!Macro3Sp_pkg"
    Sheets("Sp_Pkg").Visible = False
    Sheets("Safari").Visible = True
    Sheets("Safari").Select
    Application.Run "RT_Stock.xls!Macro2Safari"
    Application.ScreenUpdating = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Application.ScreenUpdating = True
    Application.Run "RT_Stock.xls!Macro3Safari"
    Sheets("Safari").Visible = False
    Sheets("Main").Select
    ActiveWorkbook.Protect ("R")
    End Sub

    Expect that I am able to explain what I want !!!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    28th January 2003
    Location
    India
    Posts
    1,032

    Re: Printing multiple worksheets using macros

    Hi Kaship:

    First of all use Code Tags when you post codes. Please read forum rules.

    When you say
    VB:
    Sheets("Local").Select 
    
    
    this will select or activate your sheet and then print. Avoid selecting each sheet and printing. Here two methods you can adopt.

    First One:

    VB:
    Private Sub CommandButton6_Click() 
        Application.ScreenUpdating = False 
        Sheet1.PrintOut , , 1 'use this method to print each sheet individually
        Application.ScreenUpdating = True 
         
    End Sub 
    
    
    Second One:

    VB:
    Private Sub CommandButton_Click() 
        Application.ScreenUpdating = False 
         
        Sheets(Array("Sheet1", "Sheet2", "sheet3")).PrintOut , , 1 
        Sheet1.PrintOut , , 1 'use this method to print all together at the end instead printing individually.
        Application.ScreenUpdating = True 
         
    End Sub 
    
    
    Regards


    Maqbool

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,229

    Re: Printing multiple worksheets using macros

    The reason the screenupdating lines didn't help was that they were not in the correct position in the macro. Assuming the various called macros don't turn updating on, the following should work (as Maqbool observed, you may not need to unhide and select each sheet, but that depends on what your called macros are doing).
    VB:
    Private Sub CommandButton6_Click() 
        Application.ScreenUpdating = False 
        ActiveWorkbook.Unprotect ("R") 
        Sheets("Local").Visible = True 
        Sheets("Local").Select 
        Application.Run "RT_Stock.xls!Macro2Local" 
        ActiveWindow.SelectedSheets.PrintOut Copies:=1 
        Application.Run "RT_Stock.xls!Macro3Local" 
        Sheets("Local").Visible = False 
        Sheets("NewGen").Visible = True 
        Sheets("NewGen").Select 
        Application.Run "RT_Stock.xls!Macro2NewGen" 
        ActiveWindow.SelectedSheets.PrintOut Copies:=1 
        Application.Run "RT_Stock.xls!Macro3NewGen" 
        Sheets("NewGen").Visible = False 
        Sheets("Sp_Pkg").Visible = True 
        Sheets("Sp_Pkg").Select 
        Application.Run "RT_Stock.xls!Macro2Sp_pkg" 
        ActiveWindow.SelectedSheets.PrintOut Copies:=1 
        Application.Run "RT_Stock.xls!Macro3Sp_pkg" 
        Sheets("Sp_Pkg").Visible = False 
        Sheets("Safari").Visible = True 
        Sheets("Safari").Select 
        Application.Run "RT_Stock.xls!Macro2Safari" 
        ActiveWindow.SelectedSheets.PrintOut Copies:=1 
        Application.Run "RT_Stock.xls!Macro3Safari" 
        Sheets("Safari").Visible = False 
        Sheets("Main").Select 
        ActiveWorkbook.Protect ("R") 
        Application.ScreenUpdating = True 
    End Sub 
    
    
    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

  6. #6
    Join Date
    12th March 2011
    Posts
    1

    Re: Printing multiple worksheets using macros

    For anyone looking to print multiple sheets, you have certain sheets you want to print, and you don't want to see a print preview, try this...

    VB:
    Sub PrintMultipleSheets() 
        Application.ScreenUpdating = False 
        On Error Goto N20 
        Worksheets(1).Select 
        Goto N1 
    N10: 
        Worksheets(ActiveSheet.Index + 1).Activate 
    N1: 
        If (Put criteria For identifying a sheet you Do Not want To print here. I print only sheets that have a certain value In a certain cell) Then 
            Goto N10 
        Else: End If 
            ActiveWindow.SelectedSheets.PrintOut 4, 4, 1, False, HP1300 'Note: PrintOut options [from page 4], [to page 4], [1 copy],[Preview?],[Printer]
            Application.ScreenUpdating = True 
            If Err.Number <> 0 Then 
                Worksheets(1).Activate 
            Else 
            End If 
            Goto N10 
    N20: 
        End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,897

    Re: Printing multiple worksheets using macros

    Thanks for posting your solution dnumde. In future to improve readibility, please use code tags when posting code like this [code]'your code here[/code].

Thread Information

Users Browsing this Thread

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

  1. liquiddecay

Possible Answers

  1. Printing : Printing Multiple Worksheets on a Single Page
    By excelexperts in forum EXCEL HELP
    Replies: 3
    Last Post: October 11th, 2011, 03:13
  2. Replies: 1
    Last Post: April 17th, 2008, 02:25
  3. Printing Multiple Worksheets To Pdf Format
    By kate.she in forum EXCEL HELP
    Replies: 5
    Last Post: April 11th, 2007, 18:35
  4. macros used in multiple worksheets
    By Blaine Gunther in forum EXCEL HELP
    Replies: 9
    Last Post: July 7th, 2005, 04:55
  5. Replies: 10
    Last Post: February 18th, 2004, 06:48

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