I have 2 source files (Excel) that hold different data. I would like to make a new destination file (exported file) and have a macro fetch data from both source files.

I wrote a macro for source file 1 and it seems to work well. However, when it comes to source file 2, I am having difficulties with the macro.

In addition, I would like to know how to get a macro to create a destination file (exported file) that would use data from both files source 1 and source 2.

Please see the screenshots below for more details. The yellow highlighted section is from Source file 1 and the green highlighted section is from Source file 2.

Please note that this is dummy data, not real data.

• Source 1 is the SupporterTest.xlsx
• Source 2 is the fundraise-pagesTest1.xlsx

This my macro code below:

Sub pull_columns()

    Dim head_count As Integer
    Dim row_count As Integer
    Dim col_count As Integer
    Dim i As Integer
    Dim j As Integer
    Dim ws As Worksheet
    Dim wb2 As Workbook
    
    Application.ScreenUpdating = False
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    'count headers in this workbook
    head_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlToRight)))
    
    'open other workbook and count rows and columns
    Workbooks.Open Filename:="J:\PS\FSD_Rest\SOPS_Data\Test_Rej\VBA test practices\SupporterTest.xlsx"
    ActiveWorkbook.Sheets(1).Activate
    
    row_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown)))
    col_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlToRight)))
    
    For i = 1 To head_count
        j = 1
        Do While j <= col_count
            If ws.Cells(1, i) = ActiveSheet.Cells(1, j).Text Then
                ActiveSheet.Range(Cells(1, j), Cells(row_count, j)).Copy
                ws.Cells(1, i).PasteSpecial xlPasteValues
                Application.CutCopyMode = False
                j = col_count
            End If
            j = j + 1
        Loop
    Next i
    
    ActiveWorkbook.Close Savechanges:=False
    
    ws.Cells(1, 1).Select
    
    Application.ScreenUpdating = True

End Sub