PDA

View Full Version : Code to Group Worksheets when Printing and Print Preview - 2003 vs 2010



GoKats78
03-10-2014, 03:27 AM
have been using the following code to group worksheets (and subsequently ungroup) to print (and print preview). This was developed for Excel 2003 and worked well. It works well to PRINT in Excel 2010, but doesn't work at all for the print preview function. What changes do I need to make to group the print preview as well?

I substituted Sheet1, Sheet2... for the actual sheet names in the example

To Group:



Private Sub Workbook_BeforePrint(Cancel As Boolean)
Sheets(Array("Sheet1")).Select
Cancel = True
Sheets(Array("Sheet2", "Sheet3", "Sheet4", " Sheet5", " Sheet6")).Select
Cancel = False
Application.OnTime Now, "AfterPrint"
End Sub


To Ungroup:


Private Sub AfterPrint()
'code to run after the print event
Sheets("Sheet2").Select
Range("A4").Select
End Sub

GoKats78
03-13-2014, 08:12 AM
Can anyone help me out with this?

I am really taking a huge dislike to the excel 2010...(I'm sure I am not alone on that)

Bob Phillips
03-13-2014, 09:10 AM
This works fine for me in 2010


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6")).Select
Application.OnTime Now, "AfterPrint"
End Sub


BTW, shouldn't AfterPrint be a Public procedure?

GoKats78
03-13-2014, 04:28 PM
This works fine for me in 2010


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6")).Select
Application.OnTime Now, "AfterPrint"
End Sub


BTW, shouldn't AfterPrint be a Public procedure?

It works for print, but I can't get it to work for print preview..
I don't know about your question...perhaps it should be!

Bob Phillips
03-13-2014, 04:47 PM
What part doesn't work for you? It all worked for me (after making AfterPrint public).

GoKats78
03-14-2014, 04:49 AM
The grouping for print preview didn't work...I will try making the AfterPrint Public..I am not sure how that would affect the Print Preview function.

Bob Phillips
03-14-2014, 04:50 AM
It won't, it makes the tidy-up work.