使用 VBA 格式化 Excel 工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11390300/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Formatting Excel sheet using VBA
提问by Deepak
What I have
我拥有的
Excel 2007 worksheets. Info in Range A1:C48 (fixed) in multiple sheets in the same workbook.
Excel 2007 工作表。同一工作簿中多个工作表中范围 A1:C48（固定）中的信息。
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.
我需要在具有正常边距的 Letter 尺寸纸张上打印此选择。但条件是我需要适应选择的宽度，即 3 列到 1 页。我必须保持列的宽度恒定以保持恒定的字体大小。我想使用 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.
虽然这会调整列的大小并设置其他属性，但分页符仍然保持不变，当我尝试打印它时，第 4 列也包括在内。我不想手动拖动分页符，因为这会调整字符的大小。我更喜欢设置列宽，以便它适合具有正常边距的信纸。
I would really appreciate any suggestions.
我真的很感激任何建议。
采纳答案by sturgman
If I understand correctly I think what you want is the PageSetup.PrintAreaproperty. You could add:
如果我理解正确，我认为您想要的是PageSetup.PrintArea财产。你可以添加：
.PrintArea= "$A:$C"
to your with block.
到你的块。
