Results 1 to 2 of 2

Subscript out of Range

Threaded View

  1. #1
    Registered User
    Join Date
    09-11-2012
    Location
    Chennai
    MS-Off Ver
    Excel 2007
    Posts
    4

    Subscript out of Range

    Hi Experts,

    My requirement is explained below
    step 1: (Myfile) is active workbook-->Click Button-->open the Required workbook file -->from that file ,save all sheet name in combo-list box
    step 2: Now select sheet name from combo box-->selected sheetname will be paste to "myfile" workbook




    Mycode is below


    sub maincodeButton_click()
    Call module1.callsheet
    Call module1.tocopysheet
    end sub
    -------------------------------------------------------------------




    '(Module1 code)
    Option Explicit
    Public filename As Variant, ddlfilename As Variant
    -----------------------------------------------------
    
    'To store sheets name in combo box selection
    Public Sub callsheet()
    
    Dim myname(1 To 40) As String
    Dim icount As Integer
    Dim sheetcount As Integer
    
    Sheet2.sheetnameComboBox.Clear
    
    'opening the workbook file
    filename = Application.GetOpenFilename(, , "Select Programme")
    Workbooks.Open filename
    filename = Mid(filename, InStrRev(filename, "\") + 1)
    Application.Workbooks(filename).Activate
    
    'store sheet name in the array variable and put into combo box
    sheetcount = Sheets.Count
        For icount = 1 To sheetcount
            myname(icount) = Sheets(icount).Name
            With Sheet2.sheetnameComboBox
            .AddItem myname(icount)
            End With
    Next
    End Sub
    ---------------------------------------------------------

    'copy that particular tab in another file
    Sub tocopysheet()
        Windows(filename).Activate
        Sheets(Sheet2.sheetnameComboBox.Value).Select
        Cells.Select
        Selection.Copy
        Windows("myfile").Activate
        Sheets("sheet1").Select
        Range("A1").Select
        ActiveSheet.Paste
        Windows(filename).Close
        
    End Sub
    ------------------------------------------------------------

    My problem is..

    for First time run --> code ran fine
    for Second time run--> am getting error "Subscript out of Range"
    only if i click reset button I am able to run


    This error because Windows(filename).Activate ,here where variable "filename" was initially empty while calling sub maincodeButton_click()
    Always for second run I have to Reset macro .....But i dont know how to over come...please solve my problem
    Last edited by arlu1201; 09-21-2012 at 11:45 AM.

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