Background:
I'm trying to loop through a series of sheets in a workbook from an Array given certain sheets names.
Code
Instead of the more conventional:
Sub test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
Debug.Print ws.Name
Next ws
End Sub
I'm using a different method that makes it all a bit easier to extend the array to like 10 worksheets, or start at another number, like so:
Sub test()
Dim ws As Worksheet
Dim arr As Variant: arr = ["Sheet"&ROW(1:3)]
For Each ws In ThisWorkbook.Sheets(arr)
Debug.Print ws.Name
Next ws
End Sub
Problem:
However ["Sheet"&ROW(1:3)] will create a two-dimensional array which will throw an Error 13 on the start of the For each... loop because the array expects a one-dimensional array.
I can addres this problem using a simple TRANSPOSE like:
Sub test()
Dim ws As Worksheet
Dim arr As Variant: arr = [TRANSPOSE("Sheet"&ROW(1:3))]
For Each ws In ThisWorkbook.Sheets(arr)
Debug.Print ws.Name
Next ws
End Sub
Question:
Maybe my brain is tired but right now I fail to understand why ["Sheet"&ROW(1:3)] would not create a one-dimensional array. Does anybody know?
I'm afraid the answer is simple, and I'll have one of those "Ahaaaa" moments.
ROW(1:3)is a range, a range is a two dimensional array of values.Row(1:3)is similar toRange(Cells(1,"A"),Cells(3,"XFD")).