vba 为多个工作表设置页面并打印

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/21437441/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-12 01:37:25  来源:igfitidea点击:

Page Set up for multiple worksheets and Print

excelexcel-vbavba

提问by fonzy16

I have a workbook with over a hundred sheets. I have been able to fix the column width and rows heights so that it is a good printable format for each. I'm having difficulty putting in order a code to do the following:

我有一本一百多张的工作簿。我已经能够修复列宽和行高,使其成为每个的良好可打印格式。我在整理代码以执行以下操作时遇到困难:

  1. Set the print area for each sheet [This is variable since each sheet may contain different amount of rows] - Each sheet has the same headers on the first row and the header runs from A1:N1. All the data starts at row 2 and how ever many rows down depending on each sheet.

  2. I would like the header row to be the title on each page if multiple pages need to be printed

  3. Set the format to landscape

  4. I want the scaling to fit all the columns in one page. If it has to print to multiple pages that will be if there are may rows.

  5. Print all the worksheets

  1. 为每张纸设置打印区域 [这是可变的,因为每张纸可能包含不同数量的行] - 每张纸的第一行都有相同的标题,标题从 A1:N1 开始。所有数据都从第 2 行开始,根据每张纸往下多少行。

  2. 如果需要打印多页,我希望标题行是每页的标题

  3. 将格式设置为横向

  4. 我希望缩放适合一页中的所有列。如果它必须打印到多页,如果有可能行。

  5. 打印所有工作表

I know the code below has xlPortrait as the orientation where it should be landscape. My problem is I can't get the initial part going. I've been trying to do it in pieces but I can't seem to get it started. Any help in the right direction will be great.

我知道下面的代码有 xlPortrait 作为它应该是横向的方向。我的问题是我无法进行初始部分。我一直在尝试分块进行,但似乎无法开始。任何朝着正确方向的帮助都会很棒。

 Sub SetupPrint()
Dim wkst As Worksheet
    For Each wkst In ActiveWorkbook.Sheets
        With wkst.PageSetup.PrintArea
        .Range(("A1"), Selection.SpecialCells(xlLastCell)).Select

        End With


        With wkst.PageSetup
        .PrintTitleRows = ":"
        .PrintTitleColumns = ""
        .Orientation = xlPortrait
        .FitToPagesWide = 1

        End With
    Next

End Sub

回答by user1759942

You've got basically everything there.

那里基本上什么都有。

If each worksheet has the same Number of columns and save headings, then you don't need to set the print area for each sheet. Just specify to fit all columns in on one page and that will make it print all columns on 1 page and it'll print however many pages it needs to print all the rows.

如果每个工作表具有相同的列数和保存标题,则无需为每个工作表设置打印区域。只需指定在一页上容纳所有列,这将使其在一页上打印所有列,并且它将打印打印所有行所需的页数。

so you've got your .PrintTitleRows = "$1:$1"that'll do the headings

所以你有你的.PrintTitleRows = "$1:$1"那将做标题

you've got the portrait but as stated that needs to be landscape

你有肖像,但如前所述,需要是风景

The fitToPagesWide = 1 I believe will fit all columns on each page

fitToPagesWide = 1 我相信将适合每个页面上的所有列

You could add .FitToPagesTall = Falsefor safety sake

.FitToPagesTall = False为了安全起见,您可以添加

And then the code to print is ActiveWorkbook.PrintOut Copies:=1, Collate:=True, _ IgnorePrintAreas:=False

然后打印的代码是 ActiveWorkbook.PrintOut Copies:=1, Collate:=True, _ IgnorePrintAreas:=False

I don't know what collate is.. experiment with that.

我不知道 collat​​e 是什么.. 试验一下。

Basically the way I got this code was I went to view - macro's - record macro Then I set the page layout in the workbook to what I wanted, and then I stopped the macro and checked the code and it had the code to set all the settings how I did.

基本上我得到这段代码的方式是我去查看 - 宏的 - 记录宏然后我将工作簿中的页面布局设置为我想要的,然后我停止了宏并检查了代码,它有代码来设置所有设置我是怎么做的。

Another alternative - There's no real reason to use VBA for this. go to the "Print titles" on the page layout tab to get the column headings to print on each page, then hit CTRL+P to go to print, you can set all the other print settings there including all columns on one page, then make sure it's set to "Print Entire Workbook".. that'll accomplish the same

另一种选择 - 没有真正的理由为此使用 VBA。转到页面布局选项卡上的“打印标题”以获取要在每页上打印的列标题,然后按 CTRL+P 进行打印,您可以在其中设置所有其他打印设置,包括一页上的所有列,然后确保它设置为“打印整个工作簿”..这将完成相同的