Automated cell copying

[Oldgold]

Hi

I am hoping someone can help me with a macro that can do the following, to save the painstaking process of doing it manually.

I have a workbook with about 40 sheets. I want to copy info that is always in cell H40 on each sheet to another workbook, but pasted into one sheet in the new workbook in one column (say a1 to a40). For example the info in H40 on sheet 1 of the original workbook, would be pasted to cell a1 on sheet 1 of the new workbook. Then H40 on sheet 2 of the original workbook, would be pasted to cell b1 on sheet 1 of the new workbook and so on.

I presume that a macro would be the best way to go with this. Any help would be most appreciated.

[ravishankar]

Hi
Open your orginal file(with 40+ sheets) and Enter the file name with .xls in summary file cell A1. run the macro called List.
Ravi