I have a workbook that has a varying number of sheets in it. I have a loop to put all the worksheet names into a string. The problem comes when I put the variable into a line of code I get runtime error 9, subscript out of range.
I use MsgBox (txt2) and it has the correct syntax that I am looking for, not sure why I'm getting the error
For i = 1 To Sheets.Count If Sheets(i).Visible = True Then If Trim(Sheets(i).Name) = "Summary" Then Else txt = txt & "Array(" & Chr(34) & Chr(39) & Sheets(i).Name & Chr(39) & "!R16C1:R10000C83" & Chr(34) & ", " & Chr(34) & "Item" & i - 1 & Chr(34) & "), " txt2 = txt2 & Chr(34) & Sheets(i).Name & Chr(34) & ", " End If End If Next i txt2 = Left(txt2, Len(txt2) - 2) txt2 = "Array(" & txt2 & ")" Sheets(txt2).Select 'here is where the error pops up