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

  • Thread starter Thread starter E-Town
  • Start date Start date
E

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
 
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
 
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
 
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
 
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.
 
Back
Top