Can't figure out 'Subscript out of range' error

[benben10]

So I am reasonably new to VBA and am having trouble with this error. I am trying to set up a macro button that will allow me to import a sheet of data from two separate files whos location is specified in the main workbook.

The main workbook has the following tabs: Version, Start, Summary, Summary_BEL, Current Q, Previous Q, Mapping.

The worksheets to be copied have the same name as the workbooks they are located in.

Here is what I have so far:

Public Main_wb As Workbook
 Public SheetName As String
 Public Variable As String
Private Sub CommandButton1_Click()
Dim i As Integer
Dim folderCurrent As String
Dim FileCurrent As String
Dim FolderPrevious As String
Dim FilePrevious As String
Dim Tab_name As String
Dim Destination As String
Dim Variable As String
Dim column As Integer
Set Main_wb = ThisWorkbook
Main_wb.Worksheets("Start").Activate
Main_wb.Worksheets("Start").Calculate
    
  folderCurrent = Worksheets("Start").Range("C12").Value
  FolderPrevious = Worksheets("Start").Range("C13").Value
  FileCurrent = Worksheets("Start").Range("D12").Value
  FilePrevious = Worksheets("Start").Range("D13").Value
  
    
    Call GetInputsBEN(folderCurrent, FolderPrevious, FileCurrent, FilePrevious, Tab_name)
    

End Sub



Public Function GetInputsBEN(folderCurrent As String, FileCurrent As String, FolderPrevious As String, FilePrevious As String, Tab_name As String)
 Dim myFile As String
    Dim SaveFile() As String
    Dim paramSplit() As String
    Dim d As Variant
    Dim Row As Single
    Dim k As Single
    Dim paramRows As Single
    Dim numLines As Long
    Dim NameFile As String
    Dim line As Variant
    Dim Worksheet As String
    
    
    Application.ScreenUpdating = False
    Set Main_wb = ThisWorkbook
    
    
    Main_wb.Worksheets(Tab_name).Activate
    line = Cells(Rows.Count, "B").End(xlUp).Row
    Main_wb.Worksheets(Tab_name).Range("A1:X" & line).ClearContents
    
    
    myFile1 = folderCurrent & FileCurrent
    Worksheet1 = folderCurrent & FileCurrent
     myFile2 = FolderPrevious & FilePrevious
    Worksheet2 = FolderPrevious & FilePrevious
    Open myFile1 For Input As #6
    
    Worksheets(Worksheet1).Activate
    Sheets(Worksheet1).Copy
    
    Main_wb.Worksheets("Current Q").Activate.Paste
    
   
    
    'Do While Seek(6) <= LOF(6)
     '   Line Input #6, ResultStr
      '  numLines = numLines + 1
    'Loop
    Close #6
    
    myFile2 = FolderPrevious & FilePrevious
    Worksheet2 = FolderPrevious & FilePrevious
    Open myFile2 For Input As #12
    
    Worksheets(Worksheet).Activate
    Sheets(Worksheet).Copy
    
    Main_wb.Worksheets("Previous Q").Activate.Paste
    
    
    Close #12
    
    Main_wb.Worksheets("Start").Activate
    Application.ScreenUpdating = True
End Function

 

[RoryA]

It would help if you said which line causes the error, but I'm going to guess it's this one:

 Worksheets(Worksheet).Activate

since you didn't assign a value to the Worksheet variable (not a good name for a variable by the way)

 

[Jon Peltier]

Help us help you.

What line of code is highlighted when you get the error and click Debug?

If this line is highlighted:

Main_wb.Worksheets("Start").Activate

it means there is no worksheet named "Start" in Main_wb. If it's this line:

Main_wb.Worksheets(Tab_name).Activate

then no worksheet is named with the string in Tab_name. And so forth.

Eventually you're going to have problems with lines like this as well:

Open myFile1 For Input As #6

You should Google how to use VB I/O with a variable for the file #. Alternatively, if Excel can parse the text in these files, it may be easier to open them directly in Excel, get what you need from them, and close them.

You also have a number of undeclared variables: Worksheet1, Worksheet2, myFile1, myFile2. Declaring Worksheet and myFile will not automatically declare Worksheeti and myFilei. Of course, you will not know this unless the top line of each module is

Option Explicit

which you can type manually, or have done automatically if you go to Tools menu > Options and check Require Variable Declaration. While in the Options dialog, uncheck Auto Syntax Check, or you'll be annoyed constantly.

 

[benben10]

myFile1 = folderCurrent & FileCurrent
Worksheet1 = folderCurrent & FileCurrent
myFile2 = FolderPrevious & FilePrevious
Worksheet2 = FolderPrevious & FilePrevious
Open myFile1 For Input As #6


Thanks guys, that helped to get rid of the errors. I have a new error with this line from the code above,
Open myFile1 For Input As #6

I've been trying to google some stuff to not much success.

Any help is appreciated

 

[Jon Peltier]

I saw where you had assigned values to these variables, but you had not first declared ("dimmed") them. You need something like:

Dim myFile1 As String, myFile2 As String
etc.

What do these variables look like when populated? Are they valid paths and filenames? What kind of files are they?

How did you pick #6 for your file IO? Lucky number? Hope that it's high enough not to be assigned already?

Best practice is to find a number that's not in use and use it:

Dim FileNum as Long
FileNum = FreeFile
Open myFile1 For Input As #FileNum

This file IO is pretty old, still works well and fast, but a lot of developers use FileSystemObject from the scripting runtime. It may be easier to find relevant help and examples for this than for the older approach.