title: Print specific worksheets in Excel, with different print areas and print setup using VBA
body: <p>I have a workbook with 6 different worksheets. This workbook needs to give the user the option to print 2 different sets of reports. Option 1 will print worksheets 1, 3 and 5 and Option 2 will print worksheets 2, 4 and 6. Each worksheet will require a different print range and print set up. I have approached it like this and wanted to know if this is best way to approach it (I'm sure there is a better/correct way of doing it).</p>
  
  <pre><code>Sub printingSheets()
  
  Dim Sh1 As Worksheet
  Dim Sh3 As Worksheet
  Dim Sh5 As Worksheet
  
  With ThisWorkbook
  
  Set Sh1 = .Sheets(“Dataset1”)
  Set Sh3 = .Sheets(“Dataset2”)
  Set Sh5 = .Sheets(“Dataset3”)
  
  ‘Setting the print area
  
  Sh1.PageSetup.PrintArea = "$A$1:$C$5"
  Sh3.PageSetup.PrintArea = "$A$1:$S$5"
  Sh5.PageSetup.PrintArea = "$A$1:$AA$7"
  
  ‘Setting up the print setup
  
  Sh1.PageSetup.PaperSize = xlPaperA4
  Sh3.PageSetup.PaperSize = xlPaperA4
  Sh5.PageSetup.PaperSize = xlPaperA3
  
  ‘Print
  
  Array(Sh1, Sh3, Sh5).PrintOut Copies:=1
  
  End Sub
  </code></pre>
  
  <p>And then I would write a separate macro for the other sheets.</p>
  
  <p>Thanks to anyone willing to help!</p>