Excel VBA: Copy two columns in a different file

Question

I am relatively new to VBA and have a few questions.

In the end, my code should copy data from different files into a main data sheet.

I do not know the different names of the different files (this is already covered), because there will be new generated data every once in a while. All these files are xlsx.

The only thing I am missing is that my code copies the data from different files in the range "a18 - end of column a" and "b18 - end of column b". By end of column I mean that there is no data left.

Unfortunately I do not know how to copy this and also how to store ALL the data from all the different sheets into my main sheet without overwriting the data.

Code:

Sub CopyData()
    Dim strFile As String, strPath As String, strType As String
    Dim wbX As Workbook, wksX As Worksheet, wksN As Worksheet
    Dim lngCount As Long
    Application.ScreenUpdating = False
    strPath = "C:\..."
    strType = "xlsx"
    Set wksN = ThisWorkbook.Sheets(1)
    lngCount = 1
    wksN.Range(wksN.Rows(lngCount), wksN.Rows(wksN.UsedRange.Rows.Count + lngCount)).Delete

   strFile = Dir(strPath & "\*." & strType)
   Do Until strFile = ""
       'Debug.Print (strFile)
       Set wbX = Workbooks.Open(strPath & "\" & strFile)
       Set wksX = wbX.Sheets(1)
       wksN.Cells(lngCount, 1) = wksX.Cells(1, 1)
       lngCount = lngCount + 1
       wbX.Close False
       strFile = Dir
   Loop
   Application.ScreenUpdating = True
   wksN.SaveAs Filename:="Main"
End Sub

At the moment, my code copies all the a1 cells from all the xlsx-files in my folder and pastes the content one below the other.

arun v arun v · Accepted Answer · 2018-03-13T09:42:08

Below code opens each and every file from a target folder and merge it as one,

Code:

Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
 Application.ScreenUpdating = False
 Set mergeObj = CreateObject("Scripting.FileSystemObject")
 Set dirObj = mergeObj.Getfolder("\\C:\ update the target folder")
 Set filesObj = dirObj.Files
 'Here it will open each and every file in the target folder
 For Each everyObj In filesObj
 Set bookList = Workbooks.Open(everyObj)
Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
' Paste it in the Macro sheet's non empty row
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close 
Next
End Sub

Than You

Excel VBA: Copy two columns in a different file

1 Answers