Results 1 to 2 of 2

Printing too many copies problem

Threaded View

  1. #1
    Registered User
    Join Date
    05-12-2004
    Location
    Cheltenham, UK
    Posts
    11

    Printing too many copies problem

    Can anyone help with the following macro? I'm trying to get it to print one copy each of the worksheets "po", "copy" & "site copy" but for some reason I'm getting three copies of each.

    And when I manually go file>print in this file, the printer settings default to 3 copies. Where is it getting this setting from and how do I change it?

    Sub num_audit_print()
        
        select_printer 'calls select_printer procedure
        
        Application.ScreenUpdating = False
        
        'OK to continue?
        Msg = "OK To Continue ?"
        Style = vbOKCancel + vbDefaultButton1
        Response = MsgBox(Msg, Style)
        If Response = vbOK Then
        Else
        GoTo endsub
        End If
    
        num_audit       'calls num_audit procedure
        
        Application.ScreenUpdating = True
        
    printagain:
         
        Application.ScreenUpdating = False
        
        ' Make distribution copies
        Worksheets("po").Copy after:=Worksheets("po")
        ActiveSheet.Name = "COPY"
        Worksheets("COPY").Copy after:=Worksheets("COPY")
        ActiveSheet.Name = "SITE COPY"
        
        Sheets("COPY").Select
        Range("D2:K5").Select
        ActiveCell.FormulaR1C1 = "COPY"
        Sheets("SITE COPY").Select
        Range("D2:K5").Select
        ActiveCell.FormulaR1C1 = "SITE COPY"
        Sheets("po").Select
    
        ' Print out the array of Sheets
        Sheets(Array("po", "COPY", "SITE COPY")).Select
        Sheets("po").Activate
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
        'Delete the copies temporarily turning off display alert
        Sheets(Array("COPY", "SITE COPY")).Select
        Application.DisplayAlerts = False
        ActiveWindow.SelectedSheets.Delete
        Application.DisplayAlerts = True
        
        Application.ScreenUpdating = True
        
        Msg = "Print Again ?" & Chr(13) & _
                "THIS MUST ONLY BE USED IF THE FIRST PRINT WAS NOT SUCCESSFUL"
        Style = vbYesNo + vbDefaultButton2
        Response = MsgBox(Msg, Style)
        If Response = vbYes Then
            GoTo printagain
        Else
        End If
        
        select_printer 'calls select_printer procedure
        
    endsub:
    End Sub
    Last edited by girthhart; 10-09-2008 at 10:19 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Excel 2007 : Strange gridline problem
    By wiremonkey22 in forum Excel General
    Replies: 1
    Last Post: 05-12-2008, 10:48 AM
  2. solver problem
    By haka in forum Excel General
    Replies: 3
    Last Post: 11-01-2007, 12:02 PM
  3. Calendar Formula Problem
    By roadburner in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 10-07-2007, 06:36 PM
  4. Printing Problem: Any tips to reforma?
    By leaffan27 in forum Excel General
    Replies: 3
    Last Post: 01-19-2007, 02:33 PM
  5. Macro that copies results from formula cells but not the formula itself???
    By jermsalerms in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-11-2006, 11:40 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1