Automatically import/download/copy data from one excel-file to another RRS feed

  • Question

  • Hello Everyone,

    I have many excel files with data as int the picture 1. All this excel files do not use table - it is only text.

    Additionally file1 could have diffrent number of rows.

    I wanna create a new excel file (picture 2.) and automatically import/download/copy selected columns or rows from file1 to file2.

    Do you have any idea how to do this?





    Thursday, January 11, 2018 2:22 PM

All replies

  • Hi,

    Based on  your description, you problem can be achieved by macro code. I will move your thread to Excel for Developer forum:

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.



    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Friday, January 12, 2018 2:35 AM
  • Hello AleksanderKep,

    Please refer to below code and try to adjust it for your need.

    Sub Test()
    Dim sourceWB As Workbook
    Dim sourceWS As Worksheet
    Dim targetWB As Workbook
    Dim targetWS As Worksheet
    Set targetWB = ThisWorkbook
    Set targetWS = targetWB.Worksheets(1)
    folderPath = "C:\Users\v-guaxu\Desktop\TestFolder\"
    fullNameStr = Dir(folderPath & "*.xlsx")
    While fullNameStr <> ""
    Set sourceWB = Application.Workbooks.Open(fullNameStr)
    Set sourceWS = sourceWB.Worksheets(1)
    sourceLastRow = sourceWS.Cells(sourceWS.Rows.Count, 1).End(xlUp).Row
    targetLastRow = targetWS.Cells(targetWS.Rows.Count, 1).End(xlUp).Row
    sourceWS.Rows("4:" & (sourceLastRow - 1)).Copy
    targetWS.Rows(targetLastRow).Insert Shift:=xlDown
    fullNameStr = Dir()
    Application.CutCopyMode = False
    sourceWB.Close False
    targetLastRow = targetWS.Cells(targetWS.Rows.Count, 1).End(xlUp).Row
    targetWS.Cells(targetLastRow, 6).Formula = "=Sum(F4:F" & (targetLastRow - 1) & ")"
    End Sub

    Best Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Monday, January 15, 2018 10:21 AM