VBA to Open all WorkBooks in A folder and Insert a Hidden Column

I have a directory that contains close to 1,000 workbooks (C:\Templates\) and I was wondering if it was possible to use VBA to open each of these templates one by one and either insert a hidden row from a closed workbook, or have a workbook open, and copy the column into the workbooks in the directory and then make the column hidden. Anywho, I was wanting to programatically do this as opposed to manually if possible.

Yes it is, but you'll need to give a bit more information to make this work

the macro below should get you started. It will open all excel files in the specified directory, show a message box with the file's name, then close that file.

In the place of the MsBox line you will need to insert code which identifies which file to copy data from and where to put it in the destination file, and then to hide that row.

Please Login or Register  to view this content.

Using the macro recorder, this code will copy my column from the source workbook, and paste into the desintation workbook, altho it needs some tweaking that I am unsure how to do...

Please Login or Register  to view this content.

What do you mean an addin?

There are 6 - 8 pie charts that the percentage is based off of a query...well when the query refreshes the pie chart tags do not. A workaround is to add a hidden column that references the query, then point the pie chart tag to the hidden column and it will update when the query updates. I am adding a hidden column because it is a name and a percent that I am adding so in the hidden column I combine the name and value to one, then base the pie chart tag off of that.

How would I refer to an addin to do this process? My initial plan was to add the hidden column to all workbooks, then as I go through them (I open all of them 1 a week) repoint the pie chart to the hidden column.

This probably isn't the best way, and if you have a better suggestion, I am definitely open to it!!