Please Help "Subscript out of Range" Error
MZ Tools makes life easier for the Excel VBA coder
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.

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
  •  

 

 
DMCA.com