Activate a sheet in excel 2010 vba using a variable name
You have not posted any Dim's The following works just fine:
Sub dural()
Dim Phones(1 To 2) As String
Phones(1) = "999-9999"
Phones(2) = "whatever"
Dim Phonecall As String
i = 1
Phonecall = Phones(i) & "-UnbilledData"
Sheets(Phonecall).Activate
End Sub
Related videos on Youtube
C0ppert0p
By day, I'm a lowly code monkey, eeking out an existence, converting undocumented reports, written back in the day, with Excel and Access, into something a bit more flexible using Python and SQL. By night, I perform magic with digital image processing software.
Updated on September 18, 2022Comments
-
C0ppert0p about 1 year
I am trying to activate a sheet using a variable name and am getting a subscript out of range error
the variable is declared this way
Phonecall = Phones(i) & "-UnbilledData"
when i is 1, Phonecall's value is "999-9999-UnbilledData"
This statement fails with the subscript error
Sheets(Phonecall).Activate
This statement works fine:
sheets("999-9999-UnbilledData").activate
The solution is staring me in the face, but I'm not seeing it. Any help would be appreciated.
-
Ƭᴇcʜιᴇ007 about 8 yearsMy findings as well. Perhaps OP has some unexpected whitespace in his array?
-
C0ppert0p about 8 yearsThanks, guys, The issue turns out to be getting sheet names confused with workbook names ( the workbook name is the one with extension name) Each phone had it's own workbook and each workbook had one sheet This is the code that ultimately worked for me: Dim Phones(1 To 2) As String Phones(1) = "999-9999" Phones(2) = "888-8888" For i = 1 To 2 phonecall = Phones(i) & "-UnbilledData.xls" Windows(phonecall).Activate Sheets(1).Activate Sheets(1).Move After:=Workbooks("Current.xls").Sheets(i) Next