can I make a macro to print?

[Richard Horn]

I have a summary worksheet in my Excel (2003) workbook that has multiple
sections such as, Projects Completed, Projects In Queue, Projects of Hold,
etc..

I was wondering if I could maybe place, like a print button or something,
next to each major section that would maybe have a macro or something that
would allow a user to only print that section.

The problem is if a user prints the worksheet or something, it is just way
to long, and there doesn't seem to be a way to control page breaks.

 

[Don Guillett]

Just assign to a button or shape. Change printpreview to printout

Sub printrng1()
Range("a10:c22").PrintPreview
End Sub

 

[Otto Moehrbach]

Richard
You don't provide any information about the range to be printed. The
basic macro to print a range is something like:
Sub PrintIt
Range("A1:F10").PrintOut
End Sub
This macro assumes a fixed range to be printed.
Additional code can be written in such a macro to determine, on the fly, the
range to be printed. If you need that, provide how you would determine the
range to print. HTH Otto

 

[Richard Horn]

Thanks Don, this sounds like what I want to do so using the Control Toolbox I
inserted a Command Button, and double-cliclked to edit the code. I inserted
the as you stated below, with adjusting the range of cells:

Sub printrng1()
Range("c3:h14").PrintPreview
End Sub

then used Exit the Design Mode from the Control Toolbox. I saved my
worksheet and the button is clickable, but nothing happens: range of cells
does not print or print preview to print or whatever it was exactly suppose
to do.

 

[Don Guillett]

I never use command buttons, preferring instead a shape or button from the
forms menu. Did you perhaps paste the entire 3 lines into the command button
sub? If so, should be only

 

[Richard Horn]

I must obviously really be doing something wrong. I tried the macro script
below and even tried Recording a new macro. It gave me this:

Sub CommandButton1()
'
' CommandButton1 Macro
' Macro recorded 7/15/2009 by Richard Horn
'

'
Range("C3:H14").Select
Selection.PrintOut Copies:=1, Collate:=True
End Sub

The one I recorded prints what I want it to, but here's the problem. When I
inserted a command button, copied the macro script in to the button editor,
turned the Design Mode off and saved, the control button on the worksheet
depresses, but it does not send my selection to the printer.

 

[Richard Horn]

Perfect!! Wow, that is exactly what I wanted. Thanks so much.

 

[Don Guillett]

For archival purposes show us your final solution.

 

[Richard Horn]

Final Solution:

From the Forms Toolbar insert a Button. Edit the button to insert print
macro script as follows.

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 7/15/2009 by Richard Horn
'

'
Range("C17:L50").Select
Range("L50").Activate
Selection.PrintOut Copies:=1, Collate:=True
End Sub

Edit Range (cells) where applicable.

Much easier than I thought.

 

[Richard Horn]

Don,

I do have one final question. I have rows 100 through 179 group by various
Business Units -- Data > Group and Outline. When groups they are compressed
to 11 lines, but when I apply the macro script, I would like all lines
ungrouped, and then printed, so the printed version would display all data
within the given range. Can I do that?

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 7/15/2009 by Richard Horn
'

'
Range(""C100:I179"").Select
Range("I179").Activate
Selection.PrintOut Copies:=1, Collate:=True
End Sub