"Subscript out of Range" Error

[calihiker]

Hi,

I am getting an error ""Subscript out of Range", runtime error '9' when I refer to a particular sheet in another workbook... I just change it from referring to a test sheet in the original workbook and it worked fine, but since I am referring to another workbook it's giving me errors? Anyone know why?

Here's the code... I get an error when the code goes into the first case statement...refering to another workbook "Fall03CircOrig.xls.xls"

Sub DoInsert()

Dim RowValue
RowValue = Val(UserForm007.txtRowNumber.Value)

sheetvariable = UserForm007.lblSheetLocation.Caption

Select Case sheetvariable
Case "You are on the Flax Sheet"
Windows("Fall03CircOrig.xls.xls&quot.Activate
Worksheets("Flax&quot.Rows(RowValue).Insert
Sheets("Flax&quot.Select

Range("A" & RowValue).Select
ActiveCell.Value = sheetvariable
Case "You are on the RHO Sheet"
Windows("Fall03CircOrig.xls.xls&quot.Activate
Worksheets("RHO&quot.Rows(RowValue).Insert
Sheets("RHO&quot.Select
Case "You are on the TShip Sheet"
Windows("Fall03CircOrig.xls.xls&quot.Activate
Worksheets("TShip&quot.Rows(RowValue).Insert
Sheets("TShip&quot.Select
Case Else
Windows("FLX002 Fall 2003 Final Client Merge Reports Rev2.xls&quot.Activate
Worksheets("Test Sheet&quot.Rows(RowValue).Insert
Sheets("Test Sheet&quot.Select
End Select

Range("AB" & RowValue).Select
ActiveCell.Value = v1 'adds the keycode to column AB

Range("E" & RowValue).Select
ActiveCell.Value = v1

Range("AL" & RowValue).Select
ActiveCell.Value = v3

Range("AM" & RowValue).Select
ActiveCell.Value = v4

End Sub

 

[Zathras]

Is the other workbook open? If not, it won't be in the windows collection.

Is the workbook name really "Fall03CircOrig.xls.xls" and not "Fall03CircOrig.xls"? If so, you might re-consider your naming conventions.

Run this macro to see exactly what the window names are:
[blue]

Sub ListOpenWorkbooks()
Dim wkb As Workbook
Dim s As String

  For Each wkb In Workbooks
    s = s + wkb.Name + vbNewLine
  Next wkb
  MsgBox s
End Sub

[/color]

 

[calihiker]

I did have the "Fall03CircOrig.xls" workbook open, but it wasn't named "Fall03CircOrig.xls.xls" like you had noticed. Thanks for the response and sorry, I should of noticed something like that...
It works fine now that I took off the extra .xls's.

Thanks again