VBA - 打印工作簿中的工作表文件夹

Question

随着WB

Set Sh2 = .Sheets("sheet2) 
    With Sh2.PageSetup 
     .PrintArea = "$B$2:$S$80" 
     .PaperSize = xlPaperLegal 
    End With 

Set Sh3 = .Sheets("sheet3") 
    With Sh3.PageSetup 
     .PrintArea = "$B$2:$M$104" 
     .PaperSize = xlPaperLegal 
     .Orientation = xlPortrait 
    End With 

Set execsum1 = .Sheets("sheet4") 
    With execsum1.PageSetup 
     .PrintArea = "$B$7:$N$63" 
     .PaperSize = xlPaperLegal 
     .Orientation = xlLandscape 
     .PrintTitleRows = "$B$2:$N$6" 
    End With 

Set execsum2 = .Sheets("sheet5") 
    With execsum2.PageSetup 
     .PrintArea = "$B$64:$N$106" 
     .PaperSize = xlPaperLegal 
     .Orientation = xlLandscape 
     .PrintTitleRows = "$B$2:$N$6" 
    End With 
    'ActiveSheet.PrintPreview 

Set noi1 = .Sheets("sheet6") 
    With noi1.PageSetup 
     .PrintArea = "$B$10:$N$44" 
     .PaperSize = xlPaperLegal 
     .Orientation = xlLandscape 
     .PrintTitleRows = "$B$2:$N$8" 
     .FitToPagesTall = 1 
    End With 

Set noi2 = .Sheets("sheet7") 
    With noi2.PageSetup 
     .PrintArea = "$B$46:$N$192" 
     .PaperSize = xlPaperLegal 
     .Orientation = xlLandscape 
     .PrintTitleRows = "$B$2:$N$8" 
     '.FitToPagesWide = 1 
     .FitToPagesTall = 1 
    End With 

End With 

Dim sheet As Variant 
For Each sheet In Array(execsum1, execsum2, Sh2, Sh3, noi1, noi2) 
    sheet.PrintOut Copies:=1 
Next 

    'Save and Close Workbook 
     'wb.Close SaveChanges:=False 

    'Ensure Workbook has closed before moving on to next line of code 
     DoEvents 

    'Get next file name 
     myFile = Dir 
    Loop 

'Message Box when tasks are completed 
    MsgBox "Task Complete!" 

ResetSettings: 
    'Reset Macro Optimization Settings 
    Application.EnableEvents = True 
    Application.Calculation = xlCalculationAutomatic 
    Application.ScreenUpdating = True 

End Sub 

大家好，我是新来的VBA和我想打印每文件夹中找到工作簿中的工作表6 /页。 Execsum1和execsum2来自具有不同打印区域的相同工作表;与noi1和noi2同样的故事。当我运行代码时，它将打印出第二个指定页面两次（execsum2和noi2）。为什么不打印execsum1/noi1，如果可能的话，我怎样才能使代码更有效率？谢谢。

Answer 1

它将两次打印相同的页面，因为您不是在将PageSetup从第一个版本更改为第二个版本之间打印工作表。您在此处收集工作表参考...

Set execsum1 = .Sheets("Exec Summary") 
'... 
Set execsum2 = .Sheets("Exec Summary") 

...彼此相同。一个工作表只有1 PageSetup，所以当你做到这一点...

For Each sheet In Array(execsum1, execsum2, Sh2, Sh3, noi1, noi2) 
    sheet.PrintOut Copies:=1 
Next 

...你把它设置为过去的事情。

只是完全跳过循环并单独打印每一个。循环播放它们绝对没有好处。

With execsum1.PageSetup 
    .PrintArea = "$B$7:$N$63" 
    .PaperSize = xlPaperLegal 
    .Orientation = xlLandscape 
    .PrintTitleRows = "$B$2:$N$6" 
End With 
execsum1.PrintOut Copies:=1 '<--- After each With block. 

如果要简化代码，只需解压出来的共同.PageSetup到一个函数，并通过一切作为一个参数（注意，这只是一个例子 - 我不包括你的一切”重新使用）。即：

Private Sub PrintCustomRange(sheet As Worksheet, area As String, title As String, _ 
          orient As XlPageOrientation, paper As XlPaperSize) 
    With sheet.PageSetup 
     .PrintArea = area 
     .PaperSize = paper 
     .Orientation = orient 
     If Len(title) > 0 Then .PrintTitleRows = title 
    End With 
    .PrintOut Copies:=1 
End Sub 

然后调用它像这样：

PrintCustomRange Sheets("Proforma NOI"), "$B$46:$N$192", "$B$2:$N$8", xlLandscape, xlPaperLegal