You would have to use the beforeprint event in the thisworkbook module if
you wanted it to trigger automatically
Private Sub Workbook_BeforePrint(Cancel As Boolean)
If lcase(Activesheet.Name) = "quotations" then
Hide_Print_Unhide
End if
End Sub
go to the vbe, in the project explorer double click on the thisworkbook
entry of your workbook. in the resulting module, in the left dropdown at
the top select Workbook and in the right, BeforePrint
Make it look like the above.
See Chip Pearson's page on events for an overview of Excel Events if you are
not familiar with them.
http://www.cpearson.com/excel/events.htm
--
Regards,
Tom Ogilvy
"13 Whistling Pigs" wrote in
message ...
Tom,
Thank you. It works perfectly if I go to the Tools menu and run it from
there, but it does not engage when I hit the print button or print from
the
file menu. Any ideas? Thanks again, we're a lot closer.
"Tom Ogilvy" wrote:
Sorry, looking closer, the period is hidden next to the If in my mail
software (proportional fonts - boo). But another error is you don't
have a
space before the underscore and you don't have a space before the period
(then it would have been both syntacically correct and more visible).
"If.Cells(rw,"J").Value = 0 Then_"
"If .Cells(rw,"J").Value = 0 Then _" is correct
Again, copied from my original posting and pasted into a module, it ran
with
no problems.
--
Regards,
Tom Ogilvy
"Tom Ogilvy" wrote in message
...
IN contrast, it worked fine for me, whether I ran it as a stand alone
macro
or if I ran it from a menu button. Interestingly, my macro had no
line
"If.Cells(rw,"J").Value = 0 Then_"
I had a period in front of Cells in my suggestion.
--
Regards,
Tom Ogilvy
"13 Whistling Pigs" wrote
in
message ...
Still didn't work, everything printed and when I went to run macro
off
the
tools menu, I got a compile error with "If.Cells(rw,"J").Value = 0
Then_"
line hilighted.
Any ides?
"Tom Ogilvy" wrote:
Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False
With Sheets("Quotations")
.rows.Hidden = False
For rw = 2 To 500
If .Cells(rw,"J").Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.rows.Hidden = False
End With
Application.ScreenUpdating = True
End Sub
--
Regards,
Tom Ogilvy
"13 Whistling Pigs"
wrote
in
message ...
I am trying to print out an excel sheet where only rows with
column
J
not
equal to zero print. I used the following code found on a linked
website.
When I print, I still get all 500 rows. Any idea why this code
does
not
work?
Thanks
Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False
With Sheets("Quotations")
For rw = 1 To 500
If Application.WorksheetFunction.CountJ( _
.Cells(rw, 1).Range("J1")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("J1").EntireRow.Hidden = False
End With
Application.ScreenUpdating = True
End Sub