Print Setup VBA code behind a command button

boldcode · Aug 8, 2011

Hi,

I have put together the following Print Setup VBA code behind a command button, but I can't get it to work properly:

Sub Print_Report()
Application.ScreenUpdating = False
With Worksheets("Rates").PageSetup
.DisplayPageBreaks = False
.PrintTitleRows = "A9:LastColumn"
.LeftMargin = Application.InchesToPoints(0.3)
.RightMargin = Application.InchesToPoints(0.3)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.CenterHorizontally = True
.Orientation = xlPortrait
.FirstPageNumber = xlAutomatic
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Application.ScreenUpdating = True
Selection.PrintOut Copies:=1, Collate:=True
End Sub

I need to fix some of the code to incorporate the following rules:

 I want the Macro to:
1: Include data in report from cells A5 to last column/last row
2. Print column titles on every page (Columns titles are now found on Row 9 starting from A9 to last column)

3. Autofit Columns to best Width
4. Scale Worksheet to fit all columns on One Page

Thanks,

BC

Datsmart · Aug 8, 2011

Code:

Sub Print_Report()
ActiveSheet.PageSetup.PrintArea = ActiveSheet.Range("A5").CurrentRegion.Address
lastcol = Cells(9, Columns.Count).End(xlToLeft).Column
On Error Resume Next  'continue if there are no PageBreaks
    With Worksheets("Rates").PageSetup
        .DisplayPageBreaks = False
        .PrintTitleRows = "$9:$9"  'TitleRow should only be one row, not a range of rows and columns
        .LeftMargin = Application.InchesToPoints(0.3)
        .RightMargin = Application.InchesToPoints(0.3)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .CenterHorizontally = True
        .Orientation = xlPortrait
        .FirstPageNumber = xlAutomatic
        .FitToPagesWide = 1
    End With
    Columns("A:" & lastcol).EntireColumn.AutoFit
ActiveSheet.PrintOut Copies:=1, Collate:=True
End Sub

boldcode · Aug 8, 2011

Datsmart,

I have option explicit on, do I define "lastcol" as integer?

Datsmart · Aug 8, 2011

Yes,

Code:

Dim lastcol as Integer

boldcode · Aug 8, 2011

Datsmart,

I get an error with the following piece of code:

ActiveSheet.PageSetup.PrintArea = ActiveSheet.Range("A5").CurrentRegion.Address

Its says "You've selected a single cell for the print area" and then I click ok and it prints only the contents in cell A5.

My command button is not on the "Rates" worksheet, it's actually on a a different worksheet named "control" within the same workbook.

Datsmart · Aug 8, 2011

Running the code from a different worksheet makes a difference, change code as follows:

Code:

Sub Print_Report()
Dim lastcol as Integer
Worksheets("Rates").PageSetup.PrintArea = Worksheets("Rates").Range("A9").CurrentRegion.Address
lastcol = Worksheets("Rates").Cells(9, Worksheets("Rates").Columns.Count).End(xlToLeft).Column
On Error Resume Next  'continue if there are no PageBreaks
    With Worksheets("Rates").PageSetup
        .DisplayPageBreaks = False
        .PrintTitleRows = "$9:$9"  'TitleRow should only be one row, not a range of rows and columns
        .LeftMargin = Application.InchesToPoints(0.3)
        .RightMargin = Application.InchesToPoints(0.3)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .CenterHorizontally = True
        .Orientation = xlPortrait
        .FirstPageNumber = xlAutomatic
        .FitToPagesWide = 1
    End With
    Worksheets("Rates").Columns("A:" & lastcol).EntireColumn.AutoFit
Worksheets("Rates").PrintOut Copies:=1, Collate:=True
End Sub

boldcode · Aug 8, 2011

Datsmart,

I used your new code and it works. I have a title for the report in cells A5 to C5 (in other words I merged these cells into one: A5) that's another reason why I guess the code was not working.

I guess I have to create a header for this report since I can't use merged cells. I am not sure if I am right about this or not, but I thought merged cells could be printed as well. Thoughts?

I appreciate your help. Thanks

boldcode · Aug 8, 2011

Datsmart,

Problem has been solved by Datsmart. Thank you very much.

BC

Datsmart · Aug 8, 2011

Merging cells cause many problems in Excel. I never use them. In most cases you can get the same effect by selecting the cells and format them using "Center Across Selection". Thus avoiding all the merged cell pitfalls.

Steps to Center Across Selection:
Un-Merge your cells.
Select the cells you want to Center.
Ctrl-1 will open the Format Cells Dialog box
Click the Alignment Tab
Under Text Alignment, choose "Center Across Selection" in the Horizontal: Field.
OK to complete.

boldcode · Aug 8, 2011

Thanks Datsmart, that works great!!

Print Setup VBA code behind a command button

boldcode

Active Member

Excel Facts

Datsmart

Well-known Member

boldcode

Active Member

Datsmart

Well-known Member

boldcode

Active Member

Datsmart

Well-known Member

boldcode

Active Member

boldcode

Active Member

Datsmart

Well-known Member

boldcode

Active Member

Similar threads

Forum statistics

Share this page

Print Setup VBA code behind a command button

Active Member

Excel Facts

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Active Member

Active Member

Well-known Member

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock