Copy value from another file

Question

I am trying to copy a value from a "selected file" worksheet into an already created worksheet called "Data" in cell (I5).

I wrote a code to search for number of population from previous entered country in cell (B2) in a separate worksheet called "SelectFile".

There is a problem in this command OpenBook.Sheets(1).Range(selectedRow, 3).Copy. The VBA does not read the column number.

Sub Get_Data_From_File()
    Dim FileToOpen As Variant
    Dim OpenBook As Workbook
    
    Dim countryName As Variant
    Dim Lastrow As Long
    
    Dim mainFile As Workbook
    Dim mainsheet As Worksheet
    Dim dataSheet As Worksheet
    Dim sht As Worksheet
    Dim selectedRow As Long
  
    Dim aData() As Variant
    Dim i As Long
    
    Set mainFile = ThisWorkbook
    
    Sheets("SelectFile").Activate
    Set mainsheet = ActiveSheet
    countryName = Range("B2").Value
    
    Sheets("Data").Activate
    Set dataSheet = ActiveSheet
    
    Range("I5").Clear
    
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*),*xls,(*.csv*),*csv*")
    
    If FileToOpen <> False Then
    
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        Set sht = ActiveSheet
        
        Lastrow = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row
        
        For i = 1 To Lastrow
            If Cells(i, 2) = countryName Then
                selectedRow = i
                Exit For
            End If
        Next i
        
        OpenBook.Sheets(1).Range(selectedRow, C).Copy
        
        mainFile.Activate
        dataSheet.Activate 
        
        Range("I5").Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
          False, Transpose:=True
        
        Application.DisplayAlerts = False
        
        OpenBook.Close False
        
        Application.DisplayAlerts = True
        
    End If
    Application.ScreenUpdating = True
End Sub

You say `OpenBook.Sheets(1).Range(selectedRow, 3).Copy` But the code says `OpenBook.Sheets(1).Range(selectedRow, C).Copy` But as far as I know, `Range()` doesn't accept that format. You might want `OpenBook.Sheets(1).Cells(selectedRow, 3).Copy` Or `OpenBook.Sheets(1).Range("C" & selectedRow).Copy` — Christofer Weber, Mar 28 '21 at 21:05

score 0 · Answer 1 · answered Mar 28 '21 at 21:36

You shouldn't need any activates and selects. Unless I missed something, this should be enough?

Sub Get_Data_From_File()

Dim FileToOpen As Variant
Dim mainFile As Workbook, OpenBook As Workbook
Dim countryName As Variant
Dim Lastrow As Long, i As Long

Set mainFile = ThisWorkbook
countryName = Sheets("SelectFile").Range("B2").Value

FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*),*xls,(*.csv*),*csv*")
If FileToOpen <> False Then
    Set OpenBook = Application.Workbooks.Open(FileToOpen)
    Lastrow = ActiveSheet.Cells(sht.Rows.Count, "B").End(xlUp).Row
    For i = 1 To Lastrow
        If Cells(i, 2) = countryName Then
            selectedRow = i
            Exit For
        End If
    Next i
    
    ThisWorkbook.Sheets("Data").Range("I5") = OpenBook.Sheets(1).Cells(i, 3)
    OpenBook.Close savechanges:=False
End If

End Sub

Copy value from another file

1 Answers1