Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Please Help "Subscript out of Range" Error

  1. #1
    Guest

    Default

    I recorded a macro in a workbook that will open another book, filter column E of that sheet for the number "1", select and copy those active rows and paste them into book1 and then close book2.

    It works fine when I run it as a macro alone, but when I pasted the exact macro into my "workbook_open() macro in "This Worksheet", I get a runtime error 9 "subscript out of range" error.

    The debugger highlights the 4th line from the bottom of my macro:
    Windows("Data.xls").Activate.

    Can someone please tell me how to fix this?

    Thanks in advance!

    Mav

    Here is the macro:
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
    Workbooks.Open Filename:="C:routesData.xls"
    Columns("E:E").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:="=1", Operator:=xlAnd
    Range("A2").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Windows("Route 1.xls").Activate
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
    , Transpose:=False
    Range("A1").Select
    Windows("Data.xls").Activate
    Application.CutCopyMode = False
    Range("A1").Select
    ActiveWindow.Close
    End Sub

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary Canada
    Posts
    222
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    if you check "windows.Count", it will be 1 not 2. therefore you see this error.
    paste your code in a new sub in standard module and then call it. also you can create a shortcut for it.
    try this:
    ''''''''''''''''
    Sub test()
    Dim File_For_Open As String

    Windows("Route 1.xls").Activate
    Cells.ClearContents
    File_For_Open = InputBox("Input File Name", "File Name", Default:="C:Data.xls")
    Workbooks.Open Filename:=File_For_Open
    Columns("E:E").AutoFilter Field:=1, Criteria1:="=1", Operator:=xlAnd
    Range("A2").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Windows("Route 1.xls").Activate
    Range("A1").PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
    , Transpose:=False

    Windows("Data.xls").Activate
    Application.CutCopyMode = False
    Range("A1").Select
    ActiveWindow.Close SaveChanges:=False ''' not save
    End Sub
    '''''''''''''''
    where "Route 1.xls" is a file for collecting data that "Sub Test" is in it.

    Hamid

  3. #3
    Guest

    Default

    forgive me if this seems is a dumb question, but i'm sure that you can see by the macro I posted, I am a beginner at this.

    why is "if you check "windows.Count", it will be 1 not 2." true, when both windows are open at the time of the error?

    Thanks.

    Mav

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Singapore
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It may be just the problem of your folder option setting.
    You can try this way: in Win2000, open an explorer window. Tool->Folder Option->View. untick "Hide the known file extension" Or change your code to Windows("Data.xls").Activate to Windows("Data").Activate.

    In Win98, find the folder option under View->Folder Option after opening an explorer window.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •