Results 1 to 3 of 3

Subscript error from Selecting array of sheets

Threaded View

  1. #1
    Forum Contributor
    Join Date
    01-02-2007
    Location
    Australia NSW
    MS-Off Ver
    2013
    Posts
    494

    Subscript error from Selecting array of sheets

    ActiveSheet.PageSetup.PrintArea = "$A$1:$K$179"
    UserForm1.Show
    ' Display the Print Dialog Box to select the printer required after the required print ranges have been set from the userform
    If Application.Dialogs(xlDialogPrinterSetup).Show Then
    Application.ScreenUpdating = False
    Sheets(Array("COVER", Sheet1.Range("C136").Value)).Select ' <=== Runtime error '9'   Subscript Error out of range
    Application.ScreenUpdating = False
    ActiveWindow.SelectedSheets.PrintOut
    A numerical value is selected in C136 that makes visible a sheet tab witht he same numerical value.

    The Sheets Array was working but since i added an option via a few checkboxes on a form to select a variable of range/s on the selected sheet as part of the PrintRange, it errors out now.


    A part of the code to set the PrintRange from the form is below:
      Select Case Sheets("COVER").Range("A4").Value ' <== Numerical value between 1-10 as per options below
                                    Case 10
                                        ActiveSheet.PageSetup.PrintArea = "$A$1:$AL$340"
                                        Case 9
                                        ActiveSheet.PageSetup.PrintArea = "$A$1:$AL$306"
                                            Case 8
                                            ActiveSheet.PageSetup.PrintArea = "$A$1:$AL$272"
                                                Case 7
                                                ActiveSheet.PageSetup.PrintArea = "$A$1:$AL$238"
                                                    Case 6
                                                    ActiveSheet.PageSetup.PrintArea = "$A$1:$AL$204"
                                                        Case 5
                                                        ActiveSheet.PageSetup.PrintArea = "$A$1:$AL$170"
                                                            Case 4
                                                            ActiveSheet.PageSetup.PrintArea = "$A$1:$AL$136"
                                                                Case 3
                                                                ActiveSheet.PageSetup.PrintArea = "$A$1:$AL$102"
                                                                    Case 2
                                                                    ActiveSheet.PageSetup.PrintArea = "$A$1:$AL$68"
                                                                        Case 1
                                                                        ActiveSheet.PageSetup.PrintArea = "$A$1:$AL$34"
                                                                                                                    End Select
    
    Unload Me
    Last edited by coreytroy; 12-10-2012 at 12:47 AM. Reason: spelling mistake

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1