Collating data from multiple workbook

[Manish Sharma]

Hi friends,

I have cross posted this post in another forum also, here is the link to it http://www.excelforum.com/excel-pro...-data-from-multiple-workbook.html#post4141063

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

Cells(1, 3) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Sheet1'!" & Chr(G + 64) & h

code line which pulls 20 columns of data without error is

For G = 1 To 20
Cells(1, 3) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Sheet1'!" & Chr(G + 64) & h

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

For G = 1 To 100
Cells(1, 3) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Sheet1'!" & Chr(G + 64) & h

I have attached all the three files for easy understanding (I am not able to upload files using the inbuild feature, so used 3rd party website). 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.

File name B1 http://s000.tinyupload.com/?file_id=84103687315022367881
File name B2 http://s000.tinyupload.com/?file_id=82802756364783818592
File name Consolidator http://s000.tinyupload.com/?file_id=89481127814628728288

Thanks Friends