Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
One print job as oposed to several ?
I have (with help) written the following macro.
It essentially prints the first page of every worksheet in a workbook with the exception the "import" and "client details worksheets". My problem is this macro code causes each page to be sent to the printer as a seperate job. This wouldn't be a problem but i'm in a busy office (and though quick) other printouts will sneak in between the pages. Is there any coding variation which would lead to only one job being sent to the printer. Many thanks in anctipation. James Sub PrintFullAccounts() Dim theSheet As Worksheet For Each theSheet In ActiveWorkbook.Worksheets If theSheet.Name = "import" Or theSheet.Name = "client details" Then 'do nothing Else 'print first page theSheet.PrintOut From:=1, To:=1, Copies:=1 End If Next theSheet End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
One print job as oposed to several ?
Try this:
Sub PrintAllSheets() Dim tf As Boolean, TheSheet As Worksheet, Curr As Worksheet tf = True Set Curr = ActiveSheet For Each TheSheet In Worksheets If LCase(TheSheet.Name) = "client details" Or LCase(TheSheet.Name) = "import" Then Else TheSheet.Select tf tf = False End If Next ActiveWindow.SelectedSheets.PrintOut Curr.Select End Sub "James Cornthwaite" wrote: I have (with help) written the following macro. It essentially prints the first page of every worksheet in a workbook with the exception the "import" and "client details worksheets". My problem is this macro code causes each page to be sent to the printer as a seperate job. This wouldn't be a problem but i'm in a busy office (and though quick) other printouts will sneak in between the pages. Is there any coding variation which would lead to only one job being sent to the printer. Many thanks in anctipation. James Sub PrintFullAccounts() Dim theSheet As Worksheet For Each theSheet In ActiveWorkbook.Worksheets If theSheet.Name = "import" Or theSheet.Name = "client details" Then 'do nothing Else 'print first page theSheet.PrintOut From:=1, To:=1, Copies:=1 End If Next theSheet End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
One print job as oposed to several ?
i'll give it a go and report back.
many thanks James "Bob Umlas, Excel MVP" wrote in message ... Try this: Sub PrintAllSheets() Dim tf As Boolean, TheSheet As Worksheet, Curr As Worksheet tf = True Set Curr = ActiveSheet For Each TheSheet In Worksheets If LCase(TheSheet.Name) = "client details" Or LCase(TheSheet.Name) = "import" Then Else TheSheet.Select tf tf = False End If Next ActiveWindow.SelectedSheets.PrintOut Curr.Select End Sub "James Cornthwaite" wrote: I have (with help) written the following macro. It essentially prints the first page of every worksheet in a workbook with the exception the "import" and "client details worksheets". My problem is this macro code causes each page to be sent to the printer as a seperate job. This wouldn't be a problem but i'm in a busy office (and though quick) other printouts will sneak in between the pages. Is there any coding variation which would lead to only one job being sent to the printer. Many thanks in anctipation. James Sub PrintFullAccounts() Dim theSheet As Worksheet For Each theSheet In ActiveWorkbook.Worksheets If theSheet.Name = "import" Or theSheet.Name = "client details" Then 'do nothing Else 'print first page theSheet.PrintOut From:=1, To:=1, Copies:=1 End If Next theSheet End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
One print job as oposed to several ?
Bob, sorry to be a pain, but i'm new to VB and hate using things I dont
understand. I have just read the code and am unsure how the tf boolean is significant. Why can't this just be true everytime rather than just the first iteration. (far from saying its wrong, just i dont understand !) Also what does "curr.select" at the end of the code do/mean?? Thanks again James "James Cornthwaite" wrote in message ... i'll give it a go and report back. many thanks James "Bob Umlas, Excel MVP" wrote in message ... Try this: Sub PrintAllSheets() Dim tf As Boolean, TheSheet As Worksheet, Curr As Worksheet tf = True Set Curr = ActiveSheet For Each TheSheet In Worksheets If LCase(TheSheet.Name) = "client details" Or LCase(TheSheet.Name) = "import" Then Else TheSheet.Select tf tf = False End If Next ActiveWindow.SelectedSheets.PrintOut Curr.Select End Sub "James Cornthwaite" wrote: I have (with help) written the following macro. It essentially prints the first page of every worksheet in a workbook with the exception the "import" and "client details worksheets". My problem is this macro code causes each page to be sent to the printer as a seperate job. This wouldn't be a problem but i'm in a busy office (and though quick) other printouts will sneak in between the pages. Is there any coding variation which would lead to only one job being sent to the printer. Many thanks in anctipation. James Sub PrintFullAccounts() Dim theSheet As Worksheet For Each theSheet In ActiveWorkbook.Worksheets If theSheet.Name = "import" Or theSheet.Name = "client details" Then 'do nothing Else 'print first page theSheet.PrintOut From:=1, To:=1, Copies:=1 End If Next theSheet End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
One print job as oposed to several ?
1) the first select changes from the current selection (in case it was the
Import or Client Details sheets or multiple sheets were selected). After that, it adds sheets to the selection. 2) selects only the sheet that was selected at the start of the macro. -- Regards, Tom Ogilvy "James Cornthwaite" wrote in message ... Bob, sorry to be a pain, but i'm new to VB and hate using things I dont understand. I have just read the code and am unsure how the tf boolean is significant. Why can't this just be true everytime rather than just the first iteration. (far from saying its wrong, just i dont understand !) Also what does "curr.select" at the end of the code do/mean?? Thanks again James "James Cornthwaite" wrote in message ... i'll give it a go and report back. many thanks James "Bob Umlas, Excel MVP" wrote in message ... Try this: Sub PrintAllSheets() Dim tf As Boolean, TheSheet As Worksheet, Curr As Worksheet tf = True Set Curr = ActiveSheet For Each TheSheet In Worksheets If LCase(TheSheet.Name) = "client details" Or LCase(TheSheet.Name) = "import" Then Else TheSheet.Select tf tf = False End If Next ActiveWindow.SelectedSheets.PrintOut Curr.Select End Sub "James Cornthwaite" wrote: I have (with help) written the following macro. It essentially prints the first page of every worksheet in a workbook with the exception the "import" and "client details worksheets". My problem is this macro code causes each page to be sent to the printer as a seperate job. This wouldn't be a problem but i'm in a busy office (and though quick) other printouts will sneak in between the pages. Is there any coding variation which would lead to only one job being sent to the printer. Many thanks in anctipation. James Sub PrintFullAccounts() Dim theSheet As Worksheet For Each theSheet In ActiveWorkbook.Worksheets If theSheet.Name = "import" Or theSheet.Name = "client details" Then 'do nothing Else 'print first page theSheet.PrintOut From:=1, To:=1, Copies:=1 End If Next theSheet End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
One print job as oposed to several ?
Hi there again Bob,
thanks for looking at my code and suggesting a solution. I've given it a go but cant get it to do what I want. Not just sure how to correctly modify your solution (or for that matter if it is possible) I tried your solution and it only prints one page from the whole workbook, rather than the desired first page of each worksheet within the workbook (excluding the exceptions of import and client details worksheets). If you can remember, the only reason i am going to this effort is to ensure it goes to the printer as one job as opposed to many jobs. I suspect its something to do with tf getting set to true for only ONE iteration. I would be really grateful for any help (or anybody else). Thanks James "Tom Ogilvy" wrote in message ... 1) the first select changes from the current selection (in case it was the Import or Client Details sheets or multiple sheets were selected). After that, it adds sheets to the selection. 2) selects only the sheet that was selected at the start of the macro. -- Regards, Tom Ogilvy "James Cornthwaite" wrote in message ... Bob, sorry to be a pain, but i'm new to VB and hate using things I dont understand. I have just read the code and am unsure how the tf boolean is significant. Why can't this just be true everytime rather than just the first iteration. (far from saying its wrong, just i dont understand !) Also what does "curr.select" at the end of the code do/mean?? Thanks again James "James Cornthwaite" wrote in message ... i'll give it a go and report back. many thanks James "Bob Umlas, Excel MVP" wrote in message ... Try this: Sub PrintAllSheets() Dim tf As Boolean, TheSheet As Worksheet, Curr As Worksheet tf = True Set Curr = ActiveSheet For Each TheSheet In Worksheets If LCase(TheSheet.Name) = "client details" Or LCase(TheSheet.Name) = "import" Then Else TheSheet.Select tf tf = False End If Next ActiveWindow.SelectedSheets.PrintOut Curr.Select End Sub "James Cornthwaite" wrote: I have (with help) written the following macro. It essentially prints the first page of every worksheet in a workbook with the exception the "import" and "client details worksheets". My problem is this macro code causes each page to be sent to the printer as a seperate job. This wouldn't be a problem but i'm in a busy office (and though quick) other printouts will sneak in between the pages. Is there any coding variation which would lead to only one job being sent to the printer. Many thanks in anctipation. James Sub PrintFullAccounts() Dim theSheet As Worksheet For Each theSheet In ActiveWorkbook.Worksheets If theSheet.Name = "import" Or theSheet.Name = "client details" Then 'do nothing Else 'print first page theSheet.PrintOut From:=1, To:=1, Copies:=1 End If Next theSheet End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Print and Print Preview Graphic Moving Resizing 2007/2003 | Excel Discussion (Misc queries) | |||
cell borders that I create dont show on print preview or print | Excel Discussion (Misc queries) | |||
Pivot Table macro to set print area and print details of drill down data | Excel Discussion (Misc queries) | |||
Active cell counting in particular print page (one sheet having different print area) | Excel Worksheet Functions | |||
Need Help w/ Print Macro to Print All Visible Sheets (including Charts) in a Workbook | Excel Programming |