Using VBA to print a range
 

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
--
N/A

Using VBA to print a range
 

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

Dave

*** Sent via Developersdex http://www.developersdex.com ***

Using VBA to print a range
 

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
--


Gary


"Joel" wrote in message
...
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
--
N/A

Using VBA to print a range
 

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

Joel
--
N/A


"Gary Keramidas" wrote:


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
--


Gary


"Joel" wrote in message
...
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
--
N/A

Using VBA to print a range
 

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

Joel
--
N/A


"Gary Keramidas" wrote:


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
--


Gary


"Joel" wrote in message
...
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
--
N/A

Using VBA to print a range
 

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


On Sat, 10 Nov 2007 09:17:01 -0800, Joel wrote:

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

Joel