Copy a column or columns from each workbook in a folder using VBA in Microsoft Excel
  •  The macro will copy a part of the first worksheet of every file that is in the folder C:\Data to the first worksheet of your workbook.
  • The first macro does a normal copy and second macro copy the values.

Remember Excel have only 256 columns

Sub CopyColumn ()
    Dim basebook As Workbook
    Dim mybook As Workbook
    Dim sourceRange As Range
    Dim destrange As Range
    Dim cnum As Integer
    Dim i As Long
    Dim a As Integer
    Application.ScreenUpdating = False
    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\Data"
        .SearchSubFolders = False
        .FileType = msoFileTypeExcelWorkbooks
        If .Execute() > 0 Then
            Set basebook = ThisWorkbook
            cnum = 1
            For i = 1 To .FoundFiles.Count
                Set mybook = Workbooks.Open(.FoundFiles(i))
                Set sourceRange = mybook.Worksheets(1).Columns("A:B")
                a = sourceRange.Columns.Count
                Set destrange = basebook.Worksheets(1).Cells(1, cnum)
                sourceRange.Copy destrange
                mybook.Close
                cnum = i * a + 1
            Next i
        End If
    End With
    Application.ScreenUpdating = True
End Sub

Sub CopyColumnValues()
    Dim basebook As Workbook
    Dim mybook As Workbook
    Dim sourceRange As Range
    Dim destrange As Range
    Dim cnum As Integer
    Dim i As Long
    Dim a As Integer
    Application.ScreenUpdating = False
    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\Data"
        .SearchSubFolders = False
        .FileType = msoFileTypeExcelWorkbooks
        If .Execute() > 0 Then
            Set basebook = ThisWorkbook
            cnum = 1
            For i = 1 To .FoundFiles.Count
                Set mybook = Workbooks.Open(.FoundFiles(i))
                Set sourceRange = mybook.Worksheets(1).Columns("A:B")
                a = sourceRange.Columns.Count
                With sourceRange
                    Set destrange = basebook.Worksheets(1).Columns(cnum). _
                                    Resize(, .Columns.Count)
                End With
                destrange.Value = sourceRange.Value
                mybook.Close
                cnum = i * a + 1
            Next i
        End If
    End With
    Application.ScreenUpdating = True
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube