Please Help "Subscript out of Range" Error

G

Guest

Guest
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top