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
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