Hi,

Somebody very kindly gave me this code and everything works except i would like to copy the whole sheet not just the cells contents for a given range (I need the whole sheet formatting and all) at the minute its just the cell contents getting copied within a given range.

Help! 

Sub list_files()
Dim F As String
Cells(2, 1).Select
F = Dir("C:\Documents and Settings\user name\My Documents\Test Results\" & "*.xls")
Do While Len(F) > 0
ActiveCell.Formula = F
ActiveCell.Offset(1, 0).Select
F = Dir()
Loop
x = Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
MsgBox "there are " & x - 1 & " files in this folder"
For a = 2 To x
d = 2
h = Worksheets("sheet1").Cells(a, 1)
g = Left(h, Len(h) - 4)
Sheets.Add.Name = g
Worksheets("sheet1").Activate
For c = 2 To 50 'No of rows
For b = 2 To 20 'No of columns
m = Chr(b + 64)
Worksheets("Sheet1").Cells(1, 1) = "='C:\Documents and Settings\user name\My Documents\Test Results\[" & Cells(a, 1) & "]January'!" & m & c
Worksheets(g).Cells(c + 2, b) = Worksheets("Sheet1").Cells(1, 1)
Next b
d = d + 1
Next c
Next a
MsgBox "Completed"
End Sub
Thanks
Simon.