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

Question

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

Navigation

#1 by (0 votes)

0

Using the example passed on Microsoft MSDN, the following error appears:

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

excel vba

asked by anonymous 18.03.2017 / 23:42

1 answer

Post in table N: N with node.js and angularjs Dynamic routes with angle 2

score 0 · Answer 1

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

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

The error was probably caused because of this because the reference does not exist. CAUSE 'Debugger' is indicating error on another line, please let me know!