I have over several hundred files in Excel which all, for the most part, have the same format (all columns in the same place, same header rows, etc., etc.,).
What I'd like to do is use a script/macro/something to:
1. Open each file (either manually or through some recursive method which runs through the files in the target directory)
2. Add a column to end of the range of each file (this should error out if the position of the column to be added is not equal to a constant which can be defined in the script (Column must equal Column 10)
3. That column would be titled NEWCOLUMN and then the rows (up to the last row) would be populated with the File Name of the target file.
I've attached a sample file (with fake data of course) with a BEFORE and AFTER tab for an idea of what the file starts like and what it should end like.
The files have varying rows, anywhere from 100+ to 10,000's+...