Printing a Sheet - Subscript Out of Range

jmabel01

New Member
Joined
Jan 24, 2011
Messages
10
All,

When I run this, assuming quarterNumber = 1, I get an error 9: subscript out of range.

Code:
Public Sub PrintAllAreas()

    Dim quarterNumber As String
    Dim strFile As String
    
    quarterNumber = QuarterToPrintForm.QuarterBox.Text
    
    Const strPath = "Omitted"
    
    Application.ScreenUpdating = False
    
    strFile = Dir(strPath & "*.xls")
    Do While Not strFile = ""
    With Workbooks.Open(Filename:=strPath & strFile, AddToMRU:=False)
       [COLOR=#ff0000][B] .Sheets("Sheet" & quarterNumber).PrintOut[/B][/COLOR]
        .Close SaveChanges:=False
    End With
    
    strFile = Dir
    Loop
    
    Application.ScreenUpdating = True
    
End Sub

The quarterNumber value does equal whatever I enter in the text box. I've been tinkering around with this error for over an hour and it still hasn't dawned on me yet.
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

gsistek

Well-known Member
Joined
Apr 4, 2011
Messages
660
Is there a worksheet NAMED "Sheet1" or whatever number you use?
 

jmabel01

New Member
Joined
Jan 24, 2011
Messages
10
Is there a worksheet NAMED "Sheet1" or whatever number you use?

Inside the separate workbooks the worksheets are named "Sheet1" for quarter one, "Sheet2" for quarter two, etc.

Thanks for the swift reply!
 

jmabel01

New Member
Joined
Jan 24, 2011
Messages
10
I figured it out, apparently it wasn't referring to the sheet name but rather what was being displayed on the tab "Q1", "Q2", etc. This thread can be closed.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,675
Messages
5,524,220
Members
409,565
Latest member
Suilenroc

This Week's Hot Topics

Top