如何在Excel上更改文件打印设置，以在打印工作簿时使用VBA打印几页 [英] How do I change the file print setting on Excel to use VBA to print several pages when printing the workbook

问题描述

我想在Excel中将文件打印设置更改为直接转到VBA，并在打印工作簿时打印26个工作表中的一些.大多数活动工作表应打印，但一些工作表不应打印.该工作簿是一个模板，活动的和已打印的工作表的数量将随创建的每个新工作簿而变化.最后，VBA代码是否应该放在"ThisWorkbook"部分中?

I would like to change the file print setting in Excel to go directly to VBA and print some of the 26 worksheets while printing the workbook. Most of the active worksheets should print, but a few of the worksheets should not print. The workbook is a template and the number of active and printed worksheets will change with each new workbook created. Lastly, should the VBA code go in the "ThisWorkbook" section?

推荐答案

我会使用

Sub PrintSheets ()
Dim Wks1 as Worksheet: set Wks1=Sheets("SheetName1")
Wks1.PrintOut Copies:=1, Collate:=True
'[...] and so on with your sheets

可以更改副本数的地方 为了使代码完全自动化，我通常创建一个名为宏键"的新工作表，在其中存储工作表的名称和副本计数(即A1 ="SheetName1"，B1 ="5"，A2 ="SheetName2"，B2 ="10 ").因此:

Where the no of copies can be changed To fully automatize the code I usualy create a new sheet called "Macro keys" where I store the sheets' name and copy count (i.e. A1= "SheetName1", B1="5" , A2="SheetName2", B2="10"). Thus:

Sub PrintSheets ()
Dim MacroKeys as Worksheet: Set MacroKeys = Sheets("Macrokeys")
SheetName1 = Macrokeys.Range("A1").Value
SheetName1_CopyCount = Macrokeys.Range("B1").Value
Sheet(SheetName1 ).PrintOut Copies:= SheetName1_CopyCount, Collate:=True
'[...] and so on with your sheets
End Sub

希望这会有所帮助！

这篇关于如何在Excel上更改文件打印设置，以在打印工作簿时使用VBA打印几页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！