Print range in excel

[MarkDave]

I've an excel worksheet with columns A5 - A2039 containing dates, i.e 16-Mar-2009 to 31-Dec-2014.

What I need is a print button, which when pressed will allow users to select the date ranges to be printed, (i.e from and to)

I currently have a print button with the code;

Private Sub Commandbutton1_Click()
ActiveWindow.SelectedSheets.Printout copies:=1, Collate:=True
End Sub

however, this just prints everything...i.e all 46 pages!!

any ideas? Sorry, new to this excel malarky...

Cheers!

 

[VoG]

Hello and welcome to MrExcel.

Try this - you can type in the range or select it using the mouse:

Private Sub Commandbutton1_Click()
Dim r As Range
Set r = Application.InputBox("Select the range to print")
If r Is Nothing Then Exit Sub
With ActiveSheet
    .PageSetup.PrintArea = r.Address
    .PrintOut
End With
End Sub

 

[MarkDave]

Excellent!

Thanks for this, looks good however it comes up with

"Run-time error '424':

Object required

any ideas?

 

[VoG]

Oops!

Private Sub Commandbutton1_Click()
Dim r As Range
Set r = Application.InputBox("Select the range to print", Type:=8)
If r Is Nothing Then Exit Sub
With ActiveSheet
    .PageSetup.PrintArea = r.Address
    .PrintOut
End With
End Sub

 

[MarkDave]

Thanks very much, comes up with the same error if I press cancel instead of selecting the rangeand essing ok, but I can work around that...


Thanks again!

 

[VoG]

This deals with the Cancel problem

Private Sub Commandbutton1_Click()
Dim r As Range
On Error Resume Next
Set r = Application.InputBox("Select the range to print", Type:=8)
On Error GoTo 0
If r Is Nothing Then Exit Sub
With ActiveSheet
    .PageSetup.PrintArea = r.Address
    .PrintOut
End With
End Sub

 

[MarkDave]

Perfect! Thanks for this, saved me a huge headache...

 

[MarkDave]

any ideas how I can amend this to allow users to type in dates from and to, (namely the info I have in cells A5 - A2039?

Sorry to keep changing the goal posts here...