Using VBA to print a range

[Guest]

Hi to all out there

Hi I was wondering if there is a way of writting VBA code to print a range
in Excel 2003 with a page title this would all happen from a command button.
I could use a macro but I have always been told that VBA is the better way to
go

Thanks in advance

Joel

 

[Dave Morrison]

Range("A3:C9").PrintOut Copies:=1, Collate:=True

Dave

 

[Gary Keramidas]

you can sue something like this, just change some of the settings to get what
you want:

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim rng As Range
Dim lastrow As Long
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "G").End(xlUp).Row
Application.ScreenUpdating = False
Set rng = ws.Range("A1:G" & lastrow)

With ws
With .PageSetup
.Orientation = xlPortrait
.FooterMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0#)
.LeftMargin = Application.InchesToPoints(0#)
.TopMargin = Application.InchesToPoints(0.9)
.BottomMargin = Application.InchesToPoints(0.45)
.PrintArea = rng.Address
.HeaderMargin = Application.InchesToPoints(0.25)
.CenterHeader = "&B&14Sheet1 Title"
.FitToPagesTall = 1
.FitToPagesWide = 1
.CenterHorizontally = True
End With
.PrintPreview
End With
Application.ScreenUpdating = True
End Sub

 

[Guest]

Thank you very can you please tell me how to print without the colour that is
on the spreadsheet

Joel

 

[Guest]

Thank you very can you please tell me how to print without the colour that is
on the spreadsheet

Joel

 

[Gord Dibben]

Add this line to Gerry's code.

..BlackAndWhite = True

BTW.......Macros are written using VBA so they are the same thing.


Gord Dibben MS Excel MVP