I have this workbook that, when I try to print, defaults to printing 14 copies.
Take a guess how many times I have to use my printer’s cancel button. If you guessed “every time”, then you guessed right. To fix this, I went to File - Page Setup
and clicked on the Options button
I changed the default number of copies from 14 to 1. Where this number lives will be different depending on your brand of printer, but it’s in there somewhere.
Now I can press Control+P without worry or panic
Easiest way to solve any excel printing problem
Private Sub Workbook_BeforePrint(Cancel As Boolean)
cancel = true
End Sub
So many times I have gone to collect a printout of something and found copies of my spreadsheets lying about, that some manager has decided he needs to print, that I decided to stop it.
The real question there is who’s the bright light that set that as a default in the first place! LOL!
Not that I have a need to…. but can this be set programmaticlly in VBA ?
I experienced this for the first time a couple of months ago at my new job, where one worksheet was set to 20 copies (and was a good 10 pages long). Nothing like hearing one of your new co-workers standing by the printer saying “I was wonder who’s printing all this.”
Are you saying that your workbook remembered how many copies it was set to print?
Hi Dick,
thanks for this workaround, very helpful.
I ‘ve been experiencing the same problem for a few months now where the last setting is saved and I didn’t check it before printing. Very annoying.
I can’t remember having had this kind of problem in the past. I’m using Excel 2003 in the office.
Rob, that was the case for me. It was created by somebody else a while ago and just one sheet (if I’m remembering correctly) was set to 20 copies. Excel 2003. Clearing it in a similar manner to what Dick showed fixed it.
[…] If you guessed “every time”, then you guessed right. To fix this, I went to File -… [full post] Dick Kusleika Daily Dose of Excel general 0 0 0 0 0 […]
Yes, the workbook remembers the number of copies. Or the driver does. I think the workbook does and it tells the driver. I know I set the number of copies last year to 14, but I don’t remember how. Now I don’t want to print so many, but I kept forgetting to change it.
Funny enough I couldn’t find a VBA property to change it!
@JKP
ActiveWindow.SelectedSheets.PrintOut Copies:=1
will over-ride and reset it
@Hui
Yes, I spotted that. Pity you can’t change it without printing!
thank you so much that exactly what i was looking for
Thanks for this. Very useful.
Thanks for this tips. Very useful.