Runtime error '9':Subscript out of range

dmcoffman

New Member
Joined
Jul 28, 2002
Messages
41
Got a problem:
When I open more than 1 spreadsheet I get the "Runtime error '9':Subscript out of range" error message. It does not happen opening just one??

My macro(s) are as follows:
In This Workbook:
-----
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.LeftFooter = "&10&F"
.CenterFooter = ""
.RightFooter = "&10Psafety© January 2001"
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
End Sub

------
In Module1

Sub Auto_Open()
Application.DefaultFilePath = CurDir()
With Application
.DisplayCommentIndicator = xlCommentIndicatorOnly
.Calculation = xlCalculationAutomatic
.Iteration = True
.MaxIterations = 500
.MaxChange = 0.0001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Sheets("Average Liq").Select
Range("A1").Select
End Sub
------

I've tried isolating individual lines, it appears the sheet selection is the culprit, but the recorder provides the same syntax.

(Each spreadsheet has the same macros, of course sheet name is unique.)

Ideas??? :rolleyes: :rolleyes:

Don
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Try commenting out or deleting this line:
Application.DefaultFilePath = CurDir()

Then save the workbook, close it, quit Excel, launch Excel, and try opening a few workbooks. Any luck?
 
Upvote 0
Take a close look at the *exact* spelling of the worksheet named "Average Liq". Is the sheet tab spelled exactly the way you have it in the macro? With the space in between "Average" and "Liq"? the same spelling too?
 
Upvote 0
Same thinking Tom, I used the recorder to assure spelling/spacing.

Keep in mind, if I open only one spreadsheet there is no error. This is what has me stumped....
 
Upvote 0
try reeplacing coe with this

Code:
ActiveWorkbook.PrecisionAsDisplayed = False 
Sheets("Average Liq").Select 
Range("A1").Select
Code:
With ThisWorkbook
    .PrecisionAsDisplayed = False
    .Sheets("Average Liq").Select
    .ActiveSheet.Range("A1").Select
End With
 
Upvote 0
Well when you get the run time error and hit Debug, what code line is highlighted as being in error?
 
Upvote 0
Ivan,
Tried your change. I received the following:

-----
Run-time error '1004'
Select method of Worksheet failed

--------

Tom,
In both cases debug is grayed out; not an option.

Don
 
Upvote 0
Unprotect the VBE so when the error occurs again the Debug button will be enabled (Tools > VBA Project Properties > Protection tab, deselect "Lock project for viewing", click OK), save the workbook, close it, reopen it, and see what you get now.

I would still recommend you take another look at the spelling difference between the sheet tab and the code. Maybe the tab has one space in between the two words and the code has two spaces...stuff like that.

By the way, is the sheet named Average Liq hidden? If so, unhide it before opening the workbook the next time.
 
Upvote 0
Tom/Ivan,

The solution; as Kepnor Tregoe would insist, look for change. As I pointed out the code worked when a single worksheet was opened. The problem was only evident when multiple worksheets were opened.

My belief is that Excel is opening all the workbooks before executing any code, thus it was attempting to select a sheet not available in the workbook that was active. Based on this I changed this code:

Sheets("Average Liq").Select
Range("A1").Select

-to this-

Workbooks("average liq.xls").Worksheets("Average Liq").Activate
Range("A1").Select

It may not the the best approach, but it works. Thanks guys for your ideas, I can always expect help from this forum.

Don Coffman
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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