CODEWITHSUNDEEP
vbaprintingexcelexcel-2007
Deepak
Deepak

Reputation: 1258

Formatting Excel sheet using VBA

What I have

Excel 2007 worksheets. Info in Range A1:C48 (fixed) in multiple sheets in the same workbook.

What I need

I need to print this selection in a Letter size paper with normal margins. But the condition is i am required to fit the width of the selection i.e. 3 columns to 1 page. I must keep the widths of the columns constant to maintain a constant font size. I would like to do this using VBA.

Here is the code i tried: 

Sub printing()
    Dim w1 As Single, w2 As Single, w3 As Single
    Application.ScreenUpdating = False
    w1 = 62
    w2 = 24.71
    w3 = 22.14
    For i = 1 To ActiveWorkbook.Worksheets.Count
    Worksheets(i).Activate
    ActiveSheet.Columns(1).ColumnWidth = w1
    ActiveSheet.Columns(2).ColumnWidth = w2
    ActiveSheet.Columns(3).ColumnWidth = w3

    ActiveSheet.Range("A1:C48").Select

    With Worksheets(i).PageSetup
        .PaperSize = xlPaperLetter
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1.5)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
    End With
    Next i
    Application.ScreenUpdating = True
End Sub

While this resizes the columns and sets other properties, the page break is still left untouched and when i try to print it, column 4 is also included. I don't want to manually drag the page break as this would resize the characters. I would prefer to set the column widths so that it will fit letter paper with normal margins.

I would really appreciate any suggestions.

Upvotes: 1

Views: 9506

Answers (1)

sturgman
sturgman

Reputation: 257

If I understand correctly I think what you want is the PageSetup.PrintArea property. You could add:

.PrintArea= "$A$1:$C$48"

to your with block.

Upvotes: 1

Related Questions