vba - printing worksheets in workbooks in folder

Question

With 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

Hello all, I'm new to VBA and I'm trying to print 6 worksheets/pages per workbooks found in a folder. Execsum1 and execsum2 are from the same worksheet with different print areas; same story with noi1 and noi2. When I run the code, it prints out the second designated page twice (execsum2 and noi2). Why isn't execsum1/noi1 printing out and if possible, how can I make the code more efficient? Thanks.


Show source
| excel-vba   | vba   | printing   2016-10-03 21:10 1 Answers

Answers to vba - printing worksheets in workbooks in folder ( 1 )

  1. 2016-10-03 21:10

    It prints the same sheet twice because you aren't printing the worksheet between changing the PageSetup from your first version to your second version. You collect worksheet references here...

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

    ...that are identical to each other. A worksheet only has 1 PageSetup, so when you do this...

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

    ...you get the last thing it was set to.

    Just skip the loop entirely and print each individually. There is absolutely no benefit to looping over them.

    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.
    

    If you want to simplify the code, just extract out the common .PageSetup into a function, and pass everything else as a parameter (note that this is just an example - I didn't include everything you're using). I.e.:

    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
    

    Then call it like this:

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

Leave a reply to - vba - printing worksheets in workbooks in folder

◀ Go back