Hi Prometeus,

Try this adaptation of Ron de Bruin's code:

'==============
Sub Tester()
Dim MyPath As String
Dim FilesInPath As String
Dim MyFiles() As String
Dim SourceRcount As Long
Dim Fnum As Long
Dim mybook As Workbook
Dim basebook As Workbook
Dim sourceRange As Range
Dim sourceRange2 As Range
Dim destrange As Range
Dim destRange2 As Range
Dim rnum As Long
Dim CalcMode As Long

'Fill in the path\folder where the files are
MyPath = "C:\Desktop\Data\"

'Add a slash at the end if the user forget it
If Right(MyPath, 1) < "\" Then
MyPath = MyPath & "\"
End If

'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.xls")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If

On Error GoTo CleanUp

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Set basebook = ThisWorkbook
'clear all cells on the first sheet
basebook.Worksheets(1).Cells.Clear
rnum = 1

'Fill the array(myFiles)with the list of _
'Excel files in the folder
Fnum = 0
Do While FilesInPath < ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop

'Loop through all files in the array(myFiles)
If Fnum 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
Set sourceRange = mybook.Worksheets(1).Range("F12:F18")
Set sourceRange2 = mybook.Worksheets(1).Range("G13:G18")
SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Range("A" & rnum)
Set destRange2 = basebook.Worksheets(1).Range("H" & rnum)
' This will add the workbook name in column D if you want
basebook.Worksheets(1).Cells(rnum, "D").Value = mybook.Name

sourceRange.Copy
destrange.PasteSpecial _
Paste:=xlAll, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=True

sourceRange2.Copy
destRange2.PasteSpecial _
Paste:=xlAll, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=True

rnum = rnum + 1
mybook.Close savechanges:=False
Next Fnum
End If

CleanUp:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<==============

I have used the source directory suggested in your post, but this may need
to be changed.

The code worked for my test directory files, but I suggest that you perform
a preparatory test on a limited sample opf workbooks.


---
Regards,
Norman


"Prometheus" wrote in message
oups.com...
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....