Non-Contiguous columns in multiple workbooks into New Workbook - Efficiency?

[dimsumpanda]

Hello, I would like to combine data from non-contiguous columns from multiple workbooks and paste the data together into a summary workbook. I want to combine columns A:C and column E:F, and column L from multiple workbooks (these are arbitrary columns that I picked to work with).

Looking online, I found a really helpful tutorial on how to merge data from multiple workbooks into a Summary Workbook:
https://msdn.microsoft.com/en-us/library/office/gg549168%28v=office.14%29.aspx
However, I can only seem to merge data from one range of columns at a time. I tried to use Union(DestRange, DestRange2), but DestRange2 (the second range) doesn't appear in the new workbook.

Instead, I separated the column ranges and repeated the copy and paste methods 3 times with each different range. This works, but I feel like there should be a much more efficient way of doing this. I am very new to VBA, so any help would be extremely appreciated! >_<

    Sub MergeData()    Dim SummarySheet As Worksheet
    Dim FolderPath As String
    Dim SelectedFiles() As Variant
    Dim NRow As Long
    Dim FileName As String
    Dim NFile As Long
    Dim WorkBk As Workbook
    Dim SourceRange As Range
    Dim DestRange As Range
    Dim DestRange2 As Range
    Dim DestRange3 As Range
    Dim SourceRange2 As Range
    Dim SourceRange3 As Range
    
    ' Create a new workbook and set a variable to the first sheet.
    Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    
    ' Modify this folder path to point to the files you want to use.
    FolderPath = "C:\Users\Documents\ExcelVBApractice\"
    
    ' Set the current directory to the the folder path.
    ChDrive FolderPath
    ChDir FolderPath
    
    ' Open the file dialog box and filter on Excel files, allowing multiple files
    ' to be selected.
    SelectedFiles = Application.GetOpenFilename( _
        filefilter:="Excel Files (*.xl*), *.xl*", MultiSelect:=True)
    
    ' NRow keeps track of where to insert new rows in the destination workbook.
    NRow = 1
    
    ' Loop through the list of returned file names
    For NFile = LBound(SelectedFiles) To UBound(SelectedFiles)
        ' Set FileName to be the current workbook file name to open.
        FileName = SelectedFiles(NFile)
        
        ' Open the current workbook.
        Set WorkBk = Workbooks.Open(FileName)
        


        ' Create the variable for the last row.
        Dim LastRow As Long
        LastRow = WorkBk.Worksheets(1).Cells.Find(What:="*", _
                 After:=WorkBk.Worksheets(1).Cells.Range("A1"), _
                 SearchDirection:=xlPrevious, _
                 LookIn:=xlFormulas, _
                 SearchOrder:=xlByRows).Row
        ' Set the source range, i.e. the columns to copy.
        Set SourceRange = WorkBk.Worksheets(1).Range("$A$1:$C$" & LastRow)
        Set SourceRange2 = WorkBk.Worksheets(1).Range("$E$1:$F$" & LastRow)
        Set SourceRange3 = WorkBk.Worksheets(1).Range("$L$1:$L$" & LastRow)
        
        ' Set the destination range to start at column A and be the same size as the source range.
        Set DestRange = SummarySheet.Range("A" & NRow)
        Set DestRange2 = SummarySheet.Range("D" & NRow)
        Set DestRange3 = SummarySheet.Range("F" & NRow)
        
        ' Modify this range for your workbooks. It can span multiple rows.
        Set DestRange = DestRange.Resize(SourceRange.Rows.Count, SourceRange.Columns.Count)
        Set DestRange2 = DestRange2.Resize(SourceRange2.Rows.Count, SourceRange2.Columns.Count)
        Set DestRange3 = DestRange3.Resize(SourceRange3.Rows.Count, SourceRange3.Columns.Count)
        
        ' Copy over the values from the source to the destination.
        DestRange.Value = SourceRange.Value
        DestRange2.Value = SourceRange2.Value
        DestRange3.Value = SourceRange3.Value
        
        ' Increase NRow so that we know where to copy data next.
        NRow = NRow + DestRange.Rows.Count
        
        ' Close the source workbook without saving changes.
        WorkBk.Close savechanges:=False
    Next NFile
    
    ' Call AutoFit on the destination sheet so that all data is readable.
    SummarySheet.Columns.AutoFit




End Sub

• If I am working with a lot of non-contiguous columns that appear in multiple workbooks, how can I combine them into one workbook efficiently?
• If I want to select columns singularly (instead of E:F, just column E), would I still use the Range method or the Column method?
• I attached the world fertility rate dataset that I pulled from the worldbank. I separated the information into 2 different workbooks and deleted several columns and worksheets to make the file smaller. If you run the code and select both worksheets, it should look like the whatItShouldLookLike.xlsx file.
• If I only want columns A:C, E:F (2005-2006), and column L (2012) from the two workbooks, how can I use VBA to without repeating the methods manually?