Collating data from multiple workbook

Hi Friends,

I have a file named as "consolidator", in which data is being collected from multiple files (without opening them)(in this example it pulls data from files B1 and B2; placed in the same folder where consolidator file is placed. This tool works fine when the number of columns to be pulled is less than 20, but when i make changes in the code to pull more than 20 columns of data, then it gives run-time error '1004'.

This error is coming from the line

Please Login or Register  to view this content.

code line which pulls 20 columns of data without error is

Please Login or Register  to view this content.

Code line which gives error when i try to pull 100 columns of data

Please Login or Register  to view this content.

I have attached all the three files for easy understanding. In the attached file code is for pulling 20 columns of data. Please suggest what changes i should make in the code to pull 100 columns of data present in files B1 and B2.

B1.xlsxConsolidator.xlsB2.xlsx

Thanks friends

Hi there,

The problem occurs when the data column changes from Column Z to Column AA, so using Chr(G + 64) to identify the column is not appropriate from that point onwards.

To overcome the problem in the easiest way you should change from A1 cell references to R1C1 cell references, i.e. use the following code:

Please Login or Register  to view this content.

instead of the existing code:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Thanks a lot Greg M .

I was thinking that something is wrong with 64 number in the code.
Thanks for correcting the code and the description about it.
Its working absolutely fine now.

Hi again,

Many thanks for your very prompt feedback - glad I was able to help.

Best regards,

Greg M