0

Error is "script out of range". I want range selection to be relative i.e dynamic.

Sub input_file()
    Dim FileToOpen As Variant
    Dim Openbook As Workbook
    Application.ScreenUpdating = False
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files(*.xls*),*xls*")
    If FileToOpen <> False Then
        Set Openbook = Application.Workbooks.Open(FileToOpen)
        Openbook.Sheets(2).Range("A2:AZ2").Copy
        ThisWorkbook.Worksheets("SelectFile").Range("A3").Active.PasteSpecial xlPasteValues
        OpenWorkbook.Close False
        
    End If
    Application.ScreenUpdating = True
End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
what's_in_the_name
  • 11
  • 2
  • 1
    I don't know which line is highlighted, but Subscript out of Range means that you tried to access a member of a collection or an element of an array but used a name or index that doesn't exist. Like `Sheets(-1)` or `Workbooks("Incorrect Name")` Or `MyArray(BadIndex)`. Try double checking your code during runtime and check if all of your indecies & named objects actually exist. – Toddleson Mar 24 '22 at 14:05
  • 2
    `.Range("A3").Active.PasteSpecial` - this is not valid syntax. – BigBen Mar 24 '22 at 14:08
  • If the file that you try to open has only one sheet, then this `Openbook.Sheets(2)` will throw *"subscript out of range"* because you try to access the second sheet which does not exist. Add `If Openbook.Sheets.Count < 2 Then Msgbox "Sheet(2) does not exist":Exit Sub` before the copy line. – Pᴇʜ Mar 24 '22 at 14:50
  • A side note: You can copy only values by assignment in one line: `ThisWorkbook.Worksheets("SelectFile").Range("A3:AZ3").Value = Openbook.Sheets(2).Range("A2:AZ2").Value`. – VBasic2008 Mar 24 '22 at 16:51

0 Answers0