Macro Loop to Copy and Paste Certain Specified Columns to a Destination File

[zach9208]

I need help modifying a macro. I have three separate files. These files are called "Dublin Sales", "Client Sales Tracking" and "Omnibus". I am trying to grab specific columns from the Omnibus and Client Sales Tracking files and copying the data over to the matching columns into the Dublin Sales file. I need to copy and paste the data only to the columns where the names match. I need this to loop and apply this same task to the Dublin Sales file without overlapping the data.

Note: the Omnibus file has several tabs. I need the macro to copy specific column data from "clearstream", "Julius Baer", "UBS", "UBS Fond Center", "Credit Suisse" and "Credit Suisse Sub Dist" and paste the copied data to the "All Data" tab located in the "Dublin Sales" file. In addition, as mentioned earlier, I need to copy data out of the Client Sales Tracking. The tab from this file I need to copy the specified columns from a tab called "AUM" and paste to the same "All Data" tab of the Dublin sales file without pasting overtop my earlier paste from the Omnibus file.

The below macro does the job of moving the selected columns out of the Omnibus file - Clearstream tab to the All Data tab of the Dublin Sales file. This macro requires me to have the Dublin Sales file open and browse to the Omnibus file. I need the macro to grab the columns from the all of these tabs above and paste to the destination of "All Data" within Dublin Sales file. Hope this makes sense.

Sub CopyColumnsOvertoAllData()
Dim fileToOpen As String, foundCol As Range, myCol As Variant
Dim wb1 As Workbook, wb2 As Workbook
Dim myColumns As Variant, colCopy As Long, colPaste As Long
    myColumns = Array("Date", "Account", "Units", "USD Value", "2017 Flows (USD)", "Inflows", "Outflows", _
    "Other Flows", "Parent Client", "Client", "Client Type", "Country", "Sales Person", "Distribution Team", _
    "Share Class", "Institutional or Retail", "Fund", "Performance Fee", "Omnibus", "Combined Data", "Year-End", _
    "DSM Commission", "DA Agreement", "Mgmt Fee", "Marketing/Dist", "Admin", "Rebate", "Trail (Contra Rev)", "Revenue", _
    "Trail (exp)", "Platform Fee", "Other - Networking/Admin", "Other - Introducer", "Net Revenue", "PI Distribution", _
    "RR Rev Holdings", "RR Net Rev Holdings", "RR Net Rev Net Flows", "RR Net Rev Gross Deposits", "RR Net Rev outflows", _
    "RR Trail to Distributors", "RR PI Distribution", "RR  Networking", "RR Introducer")
Set wb1 = ThisWorkbook
With wb1.Sheets("All Data")
    ''.Range("A1:BZ5000").ClearContents
    .Range("A1:BB1").Value = myColumns
End With
fileToOpen = Application.GetOpenFilename
    If fileToOpen = "False" Then Exit Sub
        Workbooks.Open Filename:=fileToOpen
Set wb2 = ActiveWorkbook
'find columns
With wb2.Sheets("Clearstream")
    For Each myCol In myColumns
        Set foundCol = .Rows(1).Find(myCol)
            If Not foundCol Is Nothing Then
                colCopy = foundCol.Column
                .Range(.Cells(2, colCopy), .Cells(10000, colCopy)).Copy
                Set foundCol = wb1.Sheets("All Data").Rows(1).Find(myCol)
                colPaste = foundCol.Column
                wb1.Sheets("All Data").Cells(2, colPaste).PasteSpecial xlPasteValues
            End If
    Next myCol
End With
Application.CutCopyMode = False
wb2.Close False
End Sub

Lastly, I should add that some of the tabs have different list of column names. ie. one file may have 12 columns that need pasted to the Dublin file, while another tab could have 20 columns that need pasted to the Dublin file. Note: the "All Data" tab of the dublin has matching column header spellings to allow for the pasting of all the columns from each of the tabs.