We believe you have sufficient information to adjust for your need now.

You said each workbook has one sheet,
you did not mention this one sheet has different names
then rng=activesheet.name serves your purpose

Cheers

"Prometheus" wrote:

Sorry If my examples seemed strange, it was the best way I knew how to
describe the set-up I'm working with.

Basically each workbook in the folder I'm working with with has several
hundred workbooks which contains the following data in this format on
the first sheet.

SheetName1


Data F12

Data F13 Data G13
Data F14 Data G14
Data F15 Data G15
Data F16 Data G16
Data F17 Data G17
Data F18 Data G18

So each workbook has all the data in the same fields - the data is
obviously different. G12 is not required.

I need to take this data range from each of the seperate workbooks in
the folder and transpose it to a main workbook in the following format.


A B C D E F
1|SheetName1|Data F12|Data F13|Data G13|Data F14|..etc..
2|SheetName2|Data F12|Data F13|Data G13|Data F14|..etc..
3|SheetName3|Data F12|Data F13|Data G13|Data F14|..etc..

So if I began with 400 workbooks in the folder, I would be left with
400 lines in the new workbook.

I hope that made sense... :-)

Norman, thanks for the offer, I may send you examples of what I'm
after.

Cheers and thanks all....