+ Reply to Thread
Results 1 to 6 of 6

print multiple sheets override print event

Hybrid View

  1. #1
    Registered User
    Join Date
    02-09-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    31

    print multiple sheets override print event

    I have a spreadsheet with multiple tabs. I would like to be able to select various tabs for printing page 1. For one specific tab I would like to filter a table, then print pages 1 & 3. I have written a macro, put it inside a workbook _BeforePrint event which runs the macro, but it prints the tab twice. Once from the print event and once by calling the macro.
    Is there a way of overwriting the print event for this specific tab when it's printed? or is there a better solution to this problem?
    Thanks in advance,
    Tim

    Sub print_pages1n3()
        ActiveSheet.PageSetup.PrintArea = "$A$1:$J$249"
        ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
            :=True, IgnorePrintAreas:=False
        ActiveSheet.PageSetup.PrintArea = "$A$1:$K$249"
        ActiveWindow.SelectedSheets.PrintOut From:=3, To:=3, Copies:=1, Collate _
            :=True, IgnorePrintAreas:=False
        Range("E30").Select
    End Sub
    Sub filterData()
    Worksheets("13-33").Activate
    ActiveSheet.ListObjects("Table8").Range.AutoFilter Field:=1, Criteria1:= _
            "<>"
    End Sub
    
    Sub Unfilter()
        Worksheets("13-33").Activate
        ActiveSheet.ListObjects("Table8").Range.AutoFilter Field:=1
    End Sub
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Cancel = True
    Call filterData
    Call print_pages1n3
    Cancel = False
    Call Unfilter
    End Sub

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: print multiple sheets override print event

    May be turn OFF the events and turn it ON in the end

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    
    Application.EnableEvents = False
        Call filterData
        Call print_pages1n3
        Call Unfilter
    Application.EnableEvents = True
    
    End Sub


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    02-09-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    31

    Re: print multiple sheets override print event

    Unfortunately that still prints page 1 twice.

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: print multiple sheets override print event

    What about adding Cancel = True before the end sub??

  5. #5
    Registered User
    Join Date
    02-09-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    31

    Re: print multiple sheets override print event

    Yes, this stops printing page 1 twice, thanks. The problem is when I select 11 tabs the print cancels after a couple of sheets. I'm assuming this cancels the print event? Can I add a conditional statement which runs the statement based on whether the sheet is active (or has been selected to print)?

  6. #6
    Registered User
    Join Date
    02-09-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    31

    Re: print multiple sheets override print event

    Solved! This was my final code if it helps.

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim ws As Worksheet
    For Each ws In ActiveWindow.SelectedSheets
    If ws.Name = "13-33" Then
                'Put your custom print code here for 'AverageEffect' tab
    Application.EnableEvents = False
        Call filterData
        Call print_pages1n3
        Call Unfilter
    Application.EnableEvents = True
    Cancel = True
    Else
    ws.PrintOut Copies:=1, Collate:=True
    End If
    Next
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Print Multiple Sheets from a Listbox on a UF as one Print Job
    By craigos in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-14-2014, 06:46 AM
  2. Print checked worksheets with sheets own print macro
    By nigelog in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-22-2013, 10:35 AM
  3. Print three sections in one print event with Macro
    By Steverizer in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-28-2013, 01:45 PM
  4. VBA to set print range, open box asking for print qty, print, then redefine print area
    By galvinpaddy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-11-2012, 06:43 PM
  5. Event on Print Preview Close and / or Print
    By christopherprater in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-19-2012, 10:29 AM

Tags for this Thread

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