Transpose columns from multiples files in mulitple subfolders into rows in a single file

Dear Excel Gurus,

I receive tax returns from different companies in the group every quarter, which needs to be consolidated. The template used by all the companies is same and the ranges to be transposed remain constant.

For examples Range F13:F30 in the "Return template" for all the companies needs to be transposed into one row for each company in the Consolidation file (rows 3:10). This action will be repeated every quarter and the data for each quarter will be pasted one below the other.

I have subfolders for each quarter e.g. D:\2013\Q#. Is there anyway that the macro will prompt me to input the subfolder name (quarter) and then copy the data from the relevant column and transpose it in the Consolidation file.

There are many questions on transposing data on this forum, but I could not find any which met my requirement. Hence I have created a fresh post.

Any help is highly appreciated. Many thanks in advance.


Vijay
Excel 2007


In the last few days, I did more google and put together the below code. This allows me to select the folder where the files are located. However everytime I run the macro, it will overwrite the numbers for the previous quarters as well.

Please can somebody have a look at the code and let me know if it is possible to do the consolidation (copy/transpose/paste) only for the current quarter. Many thanks.

Please Login or Register  to view this content.