Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
before print macro not printing multiple selected worksheets
Hello, I have the following code as before print macro in a worksheet where
all the worksheets that I would print ("meta" is one I don't print) have identical column structure & page set up (just the rows of data differ): Private Sub Workbook_BeforePrint(Cancel As Boolean) Meta = "Meta" destCell = Worksheets(Meta).Range("b43").Value Dim WS As Worksheet For Each WS In ActiveWindow.SelectedSheets 'MsgBox WS.Name & " ; " & WS.Range(destCell).Value WS.PageSetup.CenterFooter = "&8data as of " _ & Format(WS.Range(destCell).Value, "yyyy-mm-dd hh:mm:ss") _ & Chr(13) & "Page &P of &N" Next WS End Sub If I select just one worksheet to print, it prints with the footer data as expected. If I select multiple worksheets to print, this macro would go through all the worksheets to update the footers as expected *but* after all the footers are updated all the worksheets selected become de-selected except the first one selected so that only the first worksheet gets printed. Is there a way to modify the macro to make sure it will print all selected tabs? Thanks a lot. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
before print macro not printing multiple selected worksheets
Hi,
Insert the following code as the last line of your For each WS - Next code and it will print each sheet as soon as it has set the footer. WS.PrintOut Copies:=1, Collate:=True Next WS Regards, OssieMac "rockhammer" wrote: Hello, I have the following code as before print macro in a worksheet where all the worksheets that I would print ("meta" is one I don't print) have identical column structure & page set up (just the rows of data differ): Private Sub Workbook_BeforePrint(Cancel As Boolean) Meta = "Meta" destCell = Worksheets(Meta).Range("b43").Value Dim WS As Worksheet For Each WS In ActiveWindow.SelectedSheets 'MsgBox WS.Name & " ; " & WS.Range(destCell).Value WS.PageSetup.CenterFooter = "&8data as of " _ & Format(WS.Range(destCell).Value, "yyyy-mm-dd hh:mm:ss") _ & Chr(13) & "Page &P of &N" Next WS End Sub If I select just one worksheet to print, it prints with the footer data as expected. If I select multiple worksheets to print, this macro would go through all the worksheets to update the footers as expected *but* after all the footers are updated all the worksheets selected become de-selected except the first one selected so that only the first worksheet gets printed. Is there a way to modify the macro to make sure it will print all selected tabs? Thanks a lot. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
before print macro not printing multiple selected worksheets
Hi OssieMac, thanks for your suggestion. I will try that when I get in the
office tomorrow. I suppose this solution will print the page numbers and total page numbers correctly when multiple worksheets are selected. Thanks again. "OssieMac" wrote: Hi, Insert the following code as the last line of your For each WS - Next code and it will print each sheet as soon as it has set the footer. WS.PrintOut Copies:=1, Collate:=True Next WS Regards, OssieMac "rockhammer" wrote: Hello, I have the following code as before print macro in a worksheet where all the worksheets that I would print ("meta" is one I don't print) have identical column structure & page set up (just the rows of data differ): Private Sub Workbook_BeforePrint(Cancel As Boolean) Meta = "Meta" destCell = Worksheets(Meta).Range("b43").Value Dim WS As Worksheet For Each WS In ActiveWindow.SelectedSheets 'MsgBox WS.Name & " ; " & WS.Range(destCell).Value WS.PageSetup.CenterFooter = "&8data as of " _ & Format(WS.Range(destCell).Value, "yyyy-mm-dd hh:mm:ss") _ & Chr(13) & "Page &P of &N" Next WS End Sub If I select just one worksheet to print, it prints with the footer data as expected. If I select multiple worksheets to print, this macro would go through all the worksheets to update the footers as expected *but* after all the footers are updated all the worksheets selected become de-selected except the first one selected so that only the first worksheet gets printed. Is there a way to modify the macro to make sure it will print all selected tabs? Thanks a lot. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
before print macro not printing multiple selected worksheets
Hi again,
Well, I just tried it by inserting that line at the end of my for/next loop and found that: 1. it does send each of the worksheets to the printer as they get looped through 2. however, each worksheet is printed separately and so the page number and the total pages do not show what I expect when multiple worksheets are selected 3. moreover, after the completion of the for/next loop and all worksheets have been printed, the print popup window still pops up at the end So my follow-up questions a A) Is there a way to get the page numbers to reflect the multiple worksheet selection? B) Is there a way not to have the print popup window pop up at the end? C) is what I'm trying to do even possible? Thanks a lot. "rockhammer" wrote: Hi OssieMac, thanks for your suggestion. I will try that when I get in the office tomorrow. I suppose this solution will print the page numbers and total page numbers correctly when multiple worksheets are selected. Thanks again. "OssieMac" wrote: Hi, Insert the following code as the last line of your For each WS - Next code and it will print each sheet as soon as it has set the footer. WS.PrintOut Copies:=1, Collate:=True Next WS Regards, OssieMac "rockhammer" wrote: Hello, I have the following code as before print macro in a worksheet where all the worksheets that I would print ("meta" is one I don't print) have identical column structure & page set up (just the rows of data differ): Private Sub Workbook_BeforePrint(Cancel As Boolean) Meta = "Meta" destCell = Worksheets(Meta).Range("b43").Value Dim WS As Worksheet For Each WS In ActiveWindow.SelectedSheets 'MsgBox WS.Name & " ; " & WS.Range(destCell).Value WS.PageSetup.CenterFooter = "&8data as of " _ & Format(WS.Range(destCell).Value, "yyyy-mm-dd hh:mm:ss") _ & Chr(13) & "Page &P of &N" Next WS End Sub If I select just one worksheet to print, it prints with the footer data as expected. If I select multiple worksheets to print, this macro would go through all the worksheets to update the footers as expected *but* after all the footers are updated all the worksheets selected become de-selected except the first one selected so that only the first worksheet gets printed. Is there a way to modify the macro to make sure it will print all selected tabs? Thanks a lot. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
before print macro not printing multiple selected worksheets
Hi rockhammer,
Sorry the last suggestion did not do what you wanted. However, I have done some more testing with the code below. It does not deselect the sheets unless I force it to with the test line of selecting sheet (1). But anyway I have come up with a possible solution to save the selected sheets and then re-select them. Have a look at it and test it and see what it does. Note the comments. Also make sure that you are not calling another event when you get the date and time because that is the only real difference and I don't know what you have in that cell. Private Sub app_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean) Dim WS As Worksheet Dim wsSel 'Don't know what to declare as Set wsSel = ActiveWindow.SelectedSheets For Each WS In ActiveWindow.SelectedSheets 'Note:- Next code line uses 'Now' for date and time WS.PageSetup.CenterFooter = "&8data as of " _ & Format(Now, "yyyy-mm-dd hh:mm:ss") _ & Chr(13) & "Page &P of &N" Next WS Stop 'Test. Check what sheets are selected Sheets(1).Select 'Testing only Stop 'Test. Check only first sheet is selected wsSel.Select Stop 'Test. Check if all required sheets are selected Cancel = True 'Cancel print during testing. End Sub Best of luck and let me know if it works (or not). Regards, OssieMac "rockhammer" wrote: Hi again, Well, I just tried it by inserting that line at the end of my for/next loop and found that: 1. it does send each of the worksheets to the printer as they get looped through 2. however, each worksheet is printed separately and so the page number and the total pages do not show what I expect when multiple worksheets are selected 3. moreover, after the completion of the for/next loop and all worksheets have been printed, the print popup window still pops up at the end So my follow-up questions a A) Is there a way to get the page numbers to reflect the multiple worksheet selection? B) Is there a way not to have the print popup window pop up at the end? C) is what I'm trying to do even possible? Thanks a lot. "rockhammer" wrote: Hi OssieMac, thanks for your suggestion. I will try that when I get in the office tomorrow. I suppose this solution will print the page numbers and total page numbers correctly when multiple worksheets are selected. Thanks again. "OssieMac" wrote: Hi, Insert the following code as the last line of your For each WS - Next code and it will print each sheet as soon as it has set the footer. WS.PrintOut Copies:=1, Collate:=True Next WS Regards, OssieMac "rockhammer" wrote: Hello, I have the following code as before print macro in a worksheet where all the worksheets that I would print ("meta" is one I don't print) have identical column structure & page set up (just the rows of data differ): Private Sub Workbook_BeforePrint(Cancel As Boolean) Meta = "Meta" destCell = Worksheets(Meta).Range("b43").Value Dim WS As Worksheet For Each WS In ActiveWindow.SelectedSheets 'MsgBox WS.Name & " ; " & WS.Range(destCell).Value WS.PageSetup.CenterFooter = "&8data as of " _ & Format(WS.Range(destCell).Value, "yyyy-mm-dd hh:mm:ss") _ & Chr(13) & "Page &P of &N" Next WS End Sub If I select just one worksheet to print, it prints with the footer data as expected. If I select multiple worksheets to print, this macro would go through all the worksheets to update the footers as expected *but* after all the footers are updated all the worksheets selected become de-selected except the first one selected so that only the first worksheet gets printed. Is there a way to modify the macro to make sure it will print all selected tabs? Thanks a lot. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
before print macro not printing multiple selected worksheets
SUCCESS!!! Thank you very much, OssieMac!
Your trick of storing the selected worksheets and then reselecting them just before "end sub" solved my problem. I am attaching the code below so you can see what it is like. But to get back to you on your quesiton...The WS.Range(destCell).Value bit (which you replaced with Now) just goes to the cell represented by destCell (which is the same cell in each worksheet) which stores by value the time the (externally sourced) data on each worksheet was refreshed. In other words, the whole reason for me to get into this beforeprint macro business was just to show the time each worksheet was actually refreshed. Little did I know it was not that straightforward. Anyhow, your efforts are much appreciated. I was about to abandon beforeprint completely and go the different direction of a print macro attached to a button instead (see my rant under "Print all (except one or two)"). My code below. I'm not sure what Wb was for but it seemed to have worked without it. ++++++++++++++++++++++++++++++ Private Sub Workbook_BeforePrint(Cancel As Boolean) Meta = "Meta" destCell = Worksheets(Meta).Range("b43").Value Dim WS As Worksheet ' 070329 - courtesy OssieMac in microsoft.public.excel.programming ' Problem: If multiple worksheets are selected to print, beforeprint macro deselects all of them ' except the the first worksheet before it spools to the printer so that just the first ' worksheet gets printed ' Solution: Record which worksheets are selected before editing the headers/footers and then ' re-select them as the last step before "end sub" Set wsSel = ActiveWindow.SelectedSheets For Each WS In ActiveWindow.SelectedSheets 'MsgBox WS.Name & " ; " & WS.Range(destCell).Value WS.PageSetup.CenterFooter = "&8data as of " _ & Format(WS.Range(destCell).Value, "yyyy-mm-dd hh:mm:ss") _ & Chr(13) & "Page &P of &N" Next WS ' 070329 - courtesy OssieMac in microsoft.public.excel.programming ' Re-select the worksheet(s) the user selected before sending to printer wsSel.Select End Sub ++++++++++++++++++++++++++++++++ "OssieMac" wrote: Hi rockhammer, Sorry the last suggestion did not do what you wanted. However, I have done some more testing with the code below. It does not deselect the sheets unless I force it to with the test line of selecting sheet (1). But anyway I have come up with a possible solution to save the selected sheets and then re-select them. Have a look at it and test it and see what it does. Note the comments. Also make sure that you are not calling another event when you get the date and time because that is the only real difference and I don't know what you have in that cell. Private Sub app_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean) Dim WS As Worksheet Dim wsSel 'Don't know what to declare as Set wsSel = ActiveWindow.SelectedSheets For Each WS In ActiveWindow.SelectedSheets 'Note:- Next code line uses 'Now' for date and time WS.PageSetup.CenterFooter = "&8data as of " _ & Format(Now, "yyyy-mm-dd hh:mm:ss") _ & Chr(13) & "Page &P of &N" Next WS Stop 'Test. Check what sheets are selected Sheets(1).Select 'Testing only Stop 'Test. Check only first sheet is selected wsSel.Select Stop 'Test. Check if all required sheets are selected Cancel = True 'Cancel print during testing. End Sub Best of luck and let me know if it works (or not). Regards, OssieMac "rockhammer" wrote: Hi again, Well, I just tried it by inserting that line at the end of my for/next loop and found that: 1. it does send each of the worksheets to the printer as they get looped through 2. however, each worksheet is printed separately and so the page number and the total pages do not show what I expect when multiple worksheets are selected 3. moreover, after the completion of the for/next loop and all worksheets have been printed, the print popup window still pops up at the end So my follow-up questions a A) Is there a way to get the page numbers to reflect the multiple worksheet selection? B) Is there a way not to have the print popup window pop up at the end? C) is what I'm trying to do even possible? Thanks a lot. "rockhammer" wrote: Hi OssieMac, thanks for your suggestion. I will try that when I get in the office tomorrow. I suppose this solution will print the page numbers and total page numbers correctly when multiple worksheets are selected. Thanks again. "OssieMac" wrote: Hi, Insert the following code as the last line of your For each WS - Next code and it will print each sheet as soon as it has set the footer. WS.PrintOut Copies:=1, Collate:=True Next WS Regards, OssieMac "rockhammer" wrote: Hello, I have the following code as before print macro in a worksheet where all the worksheets that I would print ("meta" is one I don't print) have identical column structure & page set up (just the rows of data differ): Private Sub Workbook_BeforePrint(Cancel As Boolean) Meta = "Meta" destCell = Worksheets(Meta).Range("b43").Value Dim WS As Worksheet For Each WS In ActiveWindow.SelectedSheets 'MsgBox WS.Name & " ; " & WS.Range(destCell).Value WS.PageSetup.CenterFooter = "&8data as of " _ & Format(WS.Range(destCell).Value, "yyyy-mm-dd hh:mm:ss") _ & Chr(13) & "Page &P of &N" Next WS End Sub If I select just one worksheet to print, it prints with the footer data as expected. If I select multiple worksheets to print, this macro would go through all the worksheets to update the footers as expected *but* after all the footers are updated all the worksheets selected become de-selected except the first one selected so that only the first worksheet gets printed. Is there a way to modify the macro to make sure it will print all selected tabs? Thanks a lot. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
before print macro not printing multiple selected worksheets
O.K. Rockhammer; Another lesson.
Wb is the reference to the workbook which was activated at the time of selecting Print. it is carried forward so that it can be used in your code. Now when you have Application type events, the event will be called if you have the workbook open and then open another workbook and decide to print something from the other workbook. This could cause you real problems if you do not address the situation in the code. The following code is a demonstration to show what happens and how to handle the situation with an if statement. You probably won't need the Else; I only put that in for demo purposes. Amend your code in your workbook and then create another workbook and print from it and observe what happens and I am sure you will work out what I mean. Private Sub app_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean) Dim WS As Worksheet Dim wsSel Cancel = True 'Cancel print during testing. MsgBox "app_WorkbookBeforePrint called from " & Wb.Name If Wb.Name = "Test Multi Sheet Print.xls" Then MsgBox "Correct workbook. Therefore process" Set wsSel = ActiveWindow.SelectedSheets For Each WS In ActiveWindow.SelectedSheets 'Note:- Next code line uses 'Now' for date and time WS.PageSetup.CenterFooter = "&8data as of " _ & Format(Now, "yyyy-mm-dd hh:mm:ss") _ & Chr(13) & "Page &P of &N" Next WS wsSel.Select Else MsgBox "Processing skipped for workbook " & Wb.Name End If End Sub Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Print selected worksheets in multiple workbooks? | Excel Discussion (Misc queries) | |||
Print selected pages from multiple worksheets | Excel Discussion (Misc queries) | |||
Printing single pages from multiple worksheets in a single print job | Excel Discussion (Misc queries) | |||
How do I print a workbook in but only print selected worksheets? | Excel Discussion (Misc queries) | |||
Printing Selected Worksheets | Excel Programming |