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

Cocito

New Member
Joined
Apr 23, 2021
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
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!

VBA Code:
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
 

Attachments

  • 4-23-2021 7-32-40 AM.png
    4-23-2021 7-32-40 AM.png
    102.4 KB · Views: 6
Last edited by a moderator:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
945
Office Version
  1. 2010
Platform
  1. Windows
A smart way may be to work from a text files directory rather than from opened workbooks …​
 

Watch MrExcel Video

Forum statistics

Threads
1,133,622
Messages
5,659,926
Members
418,536
Latest member
Tezzies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top