如何在Excel中使用VBA打印所有工作表？我的代码的行为不像我认为的那样

Question

我有一个Excel工作簿，只有一个工作表要开始。然后，有一个宏从该工作表中获取数据，并为第一个工作表中的每一行创建一个新工作表。它创建的表单数量是动态的，会随着时间的推移或取决于我们设置的过滤器而增加或减少。举个例子，假设它现在产生了大约20个新表格。

我有另一个宏，旨在打印所有新创建的表，但这就是我有问题的地方。宏首先隐藏第一个工作表，然后选择所有可见的工作表，在所有这些工作表上选择一个范围，最后打印所选内容。

(旁注:我用了"selection"而不是一般的工作表或工作簿，因为打印区域不会在纸上居中。

在我的脑海里，这应该是打印出一页每页，但我不能让它正常工作。当我手动执行这些操作来记录宏时，一切正常——print命令显示它想打印19页。太棒了!但是当我回放相同的宏时，它只想打印任何活动的工作表，而不是其余的。什么好主意吗?下面是我的代码:

'Declare variables
Dim ws As Worksheet
'Temporarily hide the Customer List so the rest of the workbook can print
Worksheets("Customer List").Visible = False
'Select all visible sheets
For Each ws In Sheets
If ws.Visible Then ws.Select False
Next
'Select the range of cells on whatever sheets is active
Range("A1:J49").Select
'Promp user for which printer to use
Application.Dialogs(xlDialogPrinterSetup).Show
'Page setup
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.2)
.TopMargin = Application.InchesToPoints(0.25)
.BottomMargin = Application.InchesToPoints(0.25)
.HeaderMargin = Application.InchesToPoints(0.05)
.FooterMargin = Application.InchesToPoints(0.05)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
Application.PrintCommunication = True
'Print all visible sheets
Selection.PrintOut Copies:=1, Collate:=True
'Unhide Customer List after printing and select
Worksheets("Customer List").Visible = True
Worksheets("Customer List").Select
Range("A1").Select
End Sub

Answer 1

我自己还是个新手，但如果我要尝试这个，我会在For/Next循环中设置它，并根据表单计数。如果你的"客户名单";是第一页，那么你可以直接跳过它

AllSheets = Application.Sheets.Count
Application.Dialogs(xlDialogPrinterSetup).Show
For i=2 to AllSheets  '//Assuming sheet 1 is the Customer List
Sheets(i).Range("A1:J49").Select
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.2)
.TopMargin = Application.InchesToPoints(0.25)
.BottomMargin = Application.InchesToPoints(0.25)
.HeaderMargin = Application.InchesToPoints(0.05)
.FooterMargin = Application.InchesToPoints(0.05)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
Application.PrintCommunication = True
'Print it
Selection.PrintOut Copies:=1
Next