CODEWITHSUNDEEP
excelvba
Aswathy Ajitha
Aswathy Ajitha

Reputation: 1

I want to copy data from another workbook to my current workbook using vba code

I want to write a code similar to UDF where I hardcode a function and then create parameters for it for the code below. This is for opening an excel file and from external workbook and copy pasting the values from certain columns to the active workbook.

Private Sub Btn_Load_Test_Data_file_Click() Dim FileLocation As String Dim LastRow As Long Dim wb As Workbook Set wb = ActiveWorkbook

FileLocation = Application.GetOpenFilename
If FileLocation = "False" Then
    Beep
    Exit Sub
End If

Application.ScreenUpdating = False
Set ImportWorkbook = Workbooks.Open(Filename:=FileLocation)
LastRow = ImportWorkbook.Worksheets("Projects").Range("A7").End(xlDown).row
curr_lrow = wb.Worksheets("Projects").Range("A5").End(xlDown).row

'Copy range to clipboard
ImportWorkbook.Worksheets("Projects").Range("B7", "B" & LastRow).Copy
'PasteSpecial to paste values, formulas, formats, etc.
wb.Worksheets("Projects").Range("A" & LastRow).PasteSpecial Paste:=xlPasteValues

ImportWorkbook.Worksheets("Projects").Range("C7", "C" & LastRow).Copy
wb.Worksheets("Projects").Range("C" & LastRow).PasteSpecial Paste:=xlPasteValues

ImportWorkbook.Worksheets("Projects").Range("D7", "D" & LastRow).Copy
wb.Worksheets("Projects").Range("E" & LastRow).PasteSpecial Paste:=xlPasteValues

ImportWorkbook.Worksheets("Projects").Range("E7", "E" & LastRow).Copy
wb.Worksheets("Projects").Range("F" & LastRow).PasteSpecial Paste:=xlPasteValues

ImportWorkbook.Worksheets("Projects").Range("F7", "F" & LastRow).Copy
wb.Worksheets("Projects").Range("G" & LastRow).PasteSpecial Paste:=xlPasteValues

ImportWorkbook.Worksheets("Projects").Range("G7", "G" & LastRow).Copy
wb.Worksheets("Projects").Range("H" & LastRow).PasteSpecial Paste:=xlPasteValues

ImportWorkbook.Worksheets("Projects").Range("H7", "H" & LastRow).Copy
wb.Worksheets("Projects").Range("I" & LastRow).PasteSpecial Paste:=xlPasteValues

ImportWorkbook.Worksheets("Projects").Range("I7", "I" & LastRow).Copy
wb.Worksheets("Projects").Range("J" & LastRow).PasteSpecial Paste:=xlPasteValues

ImportWorkbook.Worksheets("Projects").Range("J7", "J" & LastRow).Copy
wb.Worksheets("Projects").Range("K" & LastRow).PasteSpecial Paste:=xlPasteValues

ImportWorkbook.Worksheets("Projects").Range("K7", "K" & LastRow).Copy
wb.Worksheets("Projects").Range("L" & LastRow).PasteSpecial Paste:=xlPasteValues

ImportWorkbook.Worksheets("Projects").Range("L7", "L" & LastRow).Copy
wb.Worksheets("Projects").Range("M" & LastRow).PasteSpecial Paste:=xlPasteValues

ImportWorkbook.Worksheets("Projects").Range("M", "M" & LastRow).Copy
wb.Worksheets("Projects").Range("N" & LastRow).PasteSpecial Paste:=xlPasteValues

ImportWorkbook.Worksheets("Projects").Range("N7", "N" & LastRow).Copy
wb.Worksheets("Projects").Range("O" & LastRow).PasteSpecial Paste:=xlPasteValues

ImportWorkbook.Worksheets("Projects").Range("O7", "O" & LastRow).Copy
wb.Worksheets("Projects").Range("P" & LastRow).PasteSpecial Paste:=xlPasteValues

ImportWorkbook.Worksheets("Projects").Range("P7", "P" & LastRow).Copy
wb.Worksheets("Projects").Range("Q" & LastRow).PasteSpecial Paste:=xlPasteValues

ImportWorkbook.Worksheets("Projects").Range("Q7", "Q" & LastRow).Copy
wb.Worksheets("Projects").Range("R" & LastRow).PasteSpecial Paste:=xlPasteValues

ImportWorkbook.Worksheets("Projects").Range("R7", "R" & LastRow).Copy
wb.Worksheets("Projects").Range("BL" & LastRow).PasteSpecial Paste:=xlPasteValues

ImportWorkbook.Worksheets("Projects").Range("S7", "S" & LastRow).Copy
wb.Worksheets("Projects").Range("BM" & LastRow).PasteSpecial Paste:=xlPasteValues

ImportWorkbook.Worksheets("Projects").Range("T7", "T" & LastRow).Copy
wb.Worksheets("Projects").Range("BN" & LastRow).PasteSpecial Paste:=xlPasteValues

ImportWorkbook.Worksheets("Projects").Range("U7", "U" & LastRow).Copy
wb.Worksheets("Projects").Range("BO" & LastRow).PasteSpecial Paste:=xlPasteValues

ImportWorkbook.Worksheets("Projects").Range("V7", "V" & LastRow).Copy
wb.Worksheets("Projects").Range("BP" & LastRow).PasteSpecial Paste:=xlPasteValues

ImportWorkbook.Worksheets("Projects").Range("W7", "W" & LastRow).Copy
wb.Worksheets("Projects").Range("BQ" & LastRow).PasteSpecial Paste:=xlPasteValues

ImportWorkbook.Worksheets("Projects").Range("X7", "X" & LastRow).Copy
wb.Worksheets("Projects").Range("BR" & LastRow).PasteSpecial Paste:=xlPasteValues

ImportWorkbook.Worksheets("Projects").Range("Y7", "Y" & LastRow).Copy
wb.Worksheets("Projects").Range("BS" & LastRow).PasteSpecial Paste:=xlPasteValues

ImportWorkbook.Worksheets("Projects").Range("Z7", "Z" & LastRow).Copy
wb.Worksheets("Projects").Range("BT" & LastRow).PasteSpecial Paste:=xlPasteValues

ImportWorkbook.Worksheets("Projects").Range("AA7", "AA" & LastRow).Copy
wb.Worksheets("Projects").Range("BU" & LastRow).PasteSpecial Paste:=xlPasteValues

ImportWorkbook.Worksheets("Projects").Range("AB7", "AB" & LastRow).Copy
wb.Worksheets("Projects").Range("BV" & LastRow).PasteSpecial Paste:=xlPasteValues

End Sub

Upvotes: 0

Views: 645

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

Try something like this:

Private Sub Btn_load_data_file_Click()
    
    Dim FileLocation As String
    Dim LastRow As Long, wsPaste As Worksheet, curr_lrow As Long
    Dim wb As Workbook, ImportWorkbook As Workbook, wsImport As Worksheet
    
    FileLocation = Application.GetOpenFilename
    If FileLocation = "False" Then
        Beep
        Exit Sub
    End If
    
    Set wb = ActiveWorkbook
    Set wsPaste = wb.Worksheets("Projects")
    
    Application.ScreenUpdating = False
    Set ImportWorkbook = Workbooks.Open(Filename:=FileLocation)
    Set wsImport = ImportWorkbook.Worksheets("Projects")
    
    LastRow = wsImport.Cells(Rows.Count, "A").End(xlUp).Row + 1 'safer than .End(xlDown)...
    curr_lrow = wsPaste.Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    CopyValues wsImport.Range("B7:B" & LastRow), wsPaste.Range("A" & curr_lrow)
    CopyValues wsImport.Range("C7:C" & LastRow), wsPaste.Range("C" & curr_lrow)
    
    ImportWorkbook.Close False
   
End Sub

'Copy values from `rngFrom` to `rngTo`
Sub CopyValues(rngFrom As Range, rngTo As Range)
    With rngFrom
        rngTo.Cells(1).Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
End Sub

Upvotes: 0

Related Questions