Activate a sheet in excel 2010 vba using a variable name

15,218

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
Share:
15,218

Related videos on Youtube

C0ppert0p
Author by

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

Comments

  • C0ppert0p
    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
    Ƭᴇcʜιᴇ007 about 8 years
    My findings as well. Perhaps OP has some unexpected whitespace in his array?
  • C0ppert0p
    C0ppert0p about 8 years
    Thanks, 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