How can i printpreview multiple excel sheets, with the names of the sheets located in a range?

Question

Good day. I was trying to print preview multiple sheets using an array. I was trying to set an array to a range and using the print preview function that way. Unfortunately it did not work.

The thing i used below:

Dim MyArray As Variant
MyArray = ThisWorkbook.Sheets("Admin Sheet").Range("A1:A2")

'A1 is "Sheet 1" and A2 is "Sheet 2"

ThisWorkbook.Sheets(Array(MyArray)).PrintPreview

Answer 1

Print Preview Multiple Sheets

Column

• You need to pass a 1D array without the use of the Array function. You can use the late-bound version of the Transpose worksheet function to convert the 2D array to a 1D array using the following:

  Dim wb As Workbook: Set wb = ThisWorkbook
  Dim MyArray As Variant: MyArray = Application _
      .Transpose(wb.Sheets("Admin Sheet").Range("A1:A2").Value)
  wb.Sheets(MyArray).PrintPreview
  

  Here MyArray holds a 1D one-based array.

Row

• If you have the sheet names in a row (e.g. A1:B1), you need to wrap the right side of the MyArray = ... expression in yet another Application.Transpose():

  MyArray = Application.Transpose(Application. _
      .Transpose(wb.Sheets("Admin Sheet").Range("A1:B1").Value))
  

  Here MyArray holds a 1D one-based array.

Arrays

• MyArray = ThisWorkbook.Sheets("Admin Sheet").Range("A1:A2").Value returns a 2D one-based (single-column) array held by the MyArray variable. You can prove it with the following:

  Debug.Print LBound(MyArray, 1), UBound(MyArray, 1), _
       LBound(MyArray, 2), UBound(MyArray, 2)
  Dim r As Long
  For r = 1 To UBound(MyArray, 1)
      Debug.Print MyArray(r, 1) ' !!!
  Next r
  

• MyArray = Application.Transpose(wb.Sheets("Admin Sheet").Range("A1:A2").Value) will return a 1D one-based array held by the MyArray variable. You can prove it with the following:

  Dim n As Long
  For n = 1 To UBound(MyArray) ' or UBound(MyArray, 1)
      Debug.Print MyArray(n) ' !!!
  Next n
  

• Dim Jag As Variant: Jag = Array(MyArray) returns the 1D MyArray in the first and only element of another 1D (usually) zero-based array held by the Jag variable. This structure is called a jagged array or an array of arrays. You can prove it using the following:

   Debug.Print LBound(Jag), UBound(Jag)
   Dim j As Long
   For j = LBound(Jag) To UBound(Jag)
        For n = LBound(Jag(j)) To UBound(Jag(j)) ' !!!
            Debug.Print j, n, Jag(j)(n) ' !!!
        Next r
   Next j