VBA Error - Run-time error 9. Subscript out of range.

[E-Town]

I have a simple Excel 2003 spreadsheet with a macro that imports another
spreadsheet and formats it. Everything has worked fine but now I'm getting
an error and it fails to import.
I've heard that there's a default printer issue with office apps, hopefully
this annoyance is fixed in office 2007 because I've seen default printer
issues in the office suite since office 97.

In this particular case changing printers simply doesn't work, and the
client is getting a bit exasperated, especially since this macro was working
fine for over 2 years.

The problematic line of code is this:

Windows(CDetail).Activate

Thanks

 

[Bob Flanagan]

Check the value of CDetail and see if any of the windows have that exact
title. You may have two windows now open on the workbook and if so the name
CDetail may work only if there is one window.

If cdetail is the name of a workbook, try workbooks(cdetail).activate
instead.

Robert Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[E-Town]

Thanks for the info, Bob. CDetail does have the right value. If the
filename was wrong the macro bombs with a different error altogether saying
it can't find the file. So far no joy, same error. Whats wierd is on my pc
if I change to certain network printers the macro works, but on other network
printers it won't run, which is why I suspected a default printer issue.
Access has a similar problem where you can't open a report unless you change
printers.



Any other ideas?

Thanks
E-Town

 

[PoppaO]

I have to lines of code that continues to fail on some workstations and not
others. The Dell laptop D610 the macro works great and the Dell D620 fails
at two specific lines of code.

Sub Getstructure(filepathname2, filename1, filename2, file_path2)
ChDrive file_path2
ChDir file_path2
Workbooks.Open Filename:=filepathname2
Sheets("Application Structure").Activate
*** Sheets("Application Structure").Copy
After:=Workbooks(filename1).Sheets(3)**
Application.DisplayAlerts = False
*** Workbooks(filename2).Close SaveChanges:=False****
Application.DisplayAlerts = True

 

[Dave Peterson]

Make sure that filename1 includes the extension (like .xls).

 

[PoppaO]

These are the two files that get created!

081120_SCVT_Application_Structure_run_08-05-14_with_effective_date_of_08-05-15.xls

081120_SCVT_Application_Structure_and_List_run_08-05-14_with_effective_date_of_08-05-15.xls

 

[Dave Peterson]

Subscript out of range means that excel can't find that item in the collection.

Maybe you don't have a worksheet named "application structure" in the active
workbook--or maybe the wrong workbook is active????

The other option is that there isn't a workbook open that has a name that is
held by Filename1.