Read first and second column from different workbooks and save this in one sheet.

Dear Excel-experts,

I have a VBA question.

Context:
- I have multiple Excel files with exactly the same layout.
- All Excel files are saved in one folder (C:\Temp\SourceFolder\*.xlm).
- Each Excel-file has 12 sheets (tabs) with month names included, such as: (2022 (1) - 2022 (2) - 2022 (3) – 2022 (4) – 2022 (5), and so on. Until the 12th month, December (2022 (12)….

What I like to achieve/have is:
- An input box with the question which sheet (tab) I like to read from all files in the source folder. For example: Which sheet you want to read? Input is (2022 (1) or 2022 (2) or 2022 (3) or a different month;
- Read the first and second columns from all Excel source-files that are placed in that specific folder (C:\Temp\SourceFolder\*.xlm).

- Make a copy of both rows (first en second one). This means per Excel-file from the source folder (C:\Temp\SourceFolder\*.xlm) and copy this information into one Excel-destination file (C:\Temp\Excel_Total_Result.xlm or something).
- Put all columns (first en second one) next to the other in the destination file (when finished, save the result).

Result: 1 destination file with all first and second columns read (from all files from the source folder), that are put next to each other in one destination file/sheet (like a summary).
Is this easy to create?

Thank you very much for any help provided. :-)


Best regards,

Hans

If I'm not mistaken to get what you mean, maybe something like this ?

Sub test()
sh = Application.InputBox("Type the name of the sheet")
i = 1

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder("D:\test")

    For Each oFile In oFolder.Files
    Set trg = ActiveWorkbook.Sheets("Sheet1").Columns(i)
    Workbooks.Open oFile
    ActiveWorkbook.Sheets(sh).Range("A:B").Copy _
                                      Destination:=trg
    i = i + 2
    ActiveWorkbook.Close SaveChanges:=False
    Next oFile
    
End Sub

Make a new workbook where you want the result is.
Put the macro above in this new workbook.
The macro assumed that all the files are in one folder, Drive D - folder test.
The result will be put in the this new workbook Sheet1.

What the macro do :
1. Ask the user to type the name of the sheet he wants the result to be put in Sheet1
2. Open each file in D:/test
3. Copy column A and B of the sheet name (inputted by the user) from the opened file
4. Paste to column A and B of Sheet1 on the first itteration
5. Close the file
6. Open the next file (point 2) and do point 3
7. Paste to column C and D of Sheet1 on the 2nd itteration
and so on until all the files in D:/test has been opened and copied.

Hi Karmapala,

Thank you for your support. :-)
I think this is almost what I am looking for.

I created a test:
I put two Excel test files in D:\Test --> 1.xls and 2.xls
- 1.xls : contains sheetname (Sheet1). Two columns with data (Column 1 has value: Pete, Colum 2 has value: John)
- 2.xls : contains sheetname (Sheet1). Two columns with data (Column 1 has value: Margret, Colum 2 has value: Frank)

I like to have all de data from 1.xls and 2.xls (both columns) in 1 file, for example:
- Result.xls: Sheetname (Sheet1), Values: Column 1: Peter, Column 2: John, Column 3: Margret, Column 4: Frank)

Depending of the amount of files in D:\Test, the results will expand (Column 5:x, Column 6:y) and so on

Is this easy to create?

Hi Karmapala,

I think that something went wrong with the language settings in my Excel version (English - > Dutch). This was my mistake.
Everything works like a charm now. So I like to thank you for your time (explanation) and effort to help me out!!!

Great support. Thank you again.

Best regards,

Hans

HI NewBee_HS,
Glad I can help, and thank you for the rep