Import workbook from another worksheet to a new workbook in the current worksheet

Question

Using the example past in Microsoft MSDN, the following error is displayed:

Run-time error '9': Subscript out of range

VBA code:

Sub Principal()
    Dim PathName As String
    Dim Filename As String
    Dim TabName As String
    Dim ControlFile As String

    ' This macro will import a file into this workbook
    Sheets("Sheet1").Select
    PathName = Range("O7").Value
    Filename = Range("O8").Value
    TabName = Range("O9").Value
    ControlFile = ActiveWorkbook.Name
    Workbooks.Open Filename:=PathName & Filename
    ActiveSheet.Name = TabName
    Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(1)
    Windows(Filename).Activate
    ActiveWorkbook.Close SaveChanges:=False
    Windows(ControlFile).Activate
End Sub

Answer 1

I believe that the problem lies in the information inserted in column "O": reading the actions performed by the code, everything indicates that the action Workbooks.Open will try to open a FileName:= "C Users User Desktoptemplate". So I suggest the following changes:

• In cell "O1", add a \ (backslash) to separate folder name from file name.
• In the "O2" cell, indicate the extension of the searched file, if .xls, .xlsx or other. The extension also makes up the file name.

The error was probably caused because of this, as the reference does not exist. If'Debugger 'is indicating error on another line, warn!