Copy variable number of open workbook's data into a single sheet

[Cocito]

What a great resource in this forum!!! A very novice VBA user here trying to find a less range bound solution for consolidating an unknown or variable number of workbooks into a new single workbook.

I want to copy many previously opened (in Excel) text files into a single worksheet in a new workbook. See image at bottom showing input and results of code snippet.

• Each text file to be copied are very small and have only 2 columns. Column A are labels, Column B are data.
• From each of the previously opened text files, copy / paste data from Column A (Labels - first time only) and Column B (data) to a new single worksheet in a new workbook.
• Loop through each of the text files until all data in their column B is copied into adjacent columns in new workbook.

• The number of open text files to import can vary from 30 to 100+
• Each text file is named for the current year and each future year. So 2021.txt represents this year's data (2021) and 2022.txt represents next year and so on.
• Starting in 2022, I no longer want to copy 2021 data into the new workbook and so on as the current year changes. IOW, I want to just import the current and future years.

My existing code below works great for a fixed and known number of input text files but not a variable and unknown number. The number of consolidated input files needs to be variable.

Also, the starting year is fixed at 2021 (2021.txt) so running this in 2022 (2022.txt) would throw an error. The starting year also needs to be variable based on the current calendar year.

Can you help with both a looping routine and handling of the start year? I have a subsequent routine that further formats the new workbook, just looking for the consolidated workbook result shown in the image.

Thanks!

Sub Consolidator_File_Aggregation()

' Consolidator_File_Aggregation Macro

' Create a new workbook to save consolidated results - LTP Results.xlsx

    Workbooks.Add
    ActiveWorkbook.SaveAs Filename:="D:\Temp\LTP Results.xlsx"
    Workbooks("LTP Results.xlsx").Activate
   
' Copy / paste labels from first (years) opened text file (column A labels) to column A in LTP Results.xlsx

    Windows("2021.TXT").Activate
    Application.WindowState = xlNormal
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LTP Results.xlsx").Activate
    Columns("A:A").Select
    ActiveSheet.Paste
    Application.WindowState = xlNormal

' Copy / paste column B data from first (years) opened text file (column B data) to column B in LTP Results.xlsx

    Windows("2021.TXT").Activate
    Columns("B:B").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LTP Results.xlsx").Activate
    Columns("B:B").Select
    ActiveSheet.Paste
    Application.WindowState = xlNormal
   
' Go to the next open text file...
' Copy / paste data from column B from the next (second year) open text file to column C in LTP Results.xlsx
   
    Windows("2022.TXT").Activate
    Columns("B:B").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LTP Results.xlsx").Activate
    Columns("C:C").Select
    ActiveSheet.Paste
    Application.WindowState = xlNormal
       
' Go to the next open text file...
' Copy / paste data from column B from the next open text file (the third and subsequent years) to the next available column in LTP Results.xlsx
' Do this loop until all of the open text file's data column B have been copied into LTP Results.xlxs
' SaveAs new workbook, LTP Results.xlxs

    Windows("2023.TXT").Activate
    Columns("B:B").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LTP Results.xlsx").Activate
    Columns("D:D").Select
    ActiveSheet.Paste
    Application.WindowState = xlNormal
   
    Windows("2024.TXT").Activate
    Columns("B:B").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LTP Results.xlsx").Activate
    Columns("E:E").Select
    ActiveSheet.Paste
    Application.WindowState = xlNormal
       
    Windows("2025.TXT").Activate
    Columns("B:B").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("LTP Results.xlsx").Activate
    Columns("F:F").Select
    ActiveSheet.Paste
    Application.WindowState = xlNormal

 

[Marc L]

​

A smart way may be to work from a text files directory rather than from opened workbooks …​