Try this (tweaking paths & file names etc)

Sub SummariseYear()

Application.DisplayAlerts = False

Dim xwb As Workbook
Dim xws As Worksheet
Set xwb = ActiveWorkbook
Set xws = xwb.Worksheets("a")

Dim wb As Workbook
Dim ws As Worksheet

Dim sPath As String
Dim sFile(11) As String

sFile(0) = "Jan.xls"
sFile(1) = "Feb.xls"
sFile(2) = "Mar.xls"
sFile(3) = "Apr.xls"
sFile(4) = "May.xls"
sFile(5) = "Jun.xls"
sFile(6) = "Jul.xls"
sFile(7) = "Aug.xls"
sFile(8) = "Sep.xls"
sFile(9) = "Oct.xls"
sFile(10) = "Nov.xls"
sFile(11) = "Dec.xls"

Dim i As Integer

sPath = "C:\path\"

For i = 1 To 12
Set wb = Workbooks.Open(sPath & sFile(i))
For Each ws In wb.Worksheets
xws.Range("f2").Offset(j, 0).Value = ws.Range("d12").Value
xws.Range("g2").Offset(j, 0).Value = ws.Range("e12").Value
xws.Range("d2").Offset(j, 0).Value = ws.Range("m12").Value
j = j + 1
Next ws
wb.Close
Next i

Application.DisplayAlerts = True

End Sub


"LiAD" wrote:

Hi,

I have a list of data contained in a series of files stored on a network.
There is one file for every month, ( file name Jan, feb etc). Each file has
31 sheets (sheet names 1,2,3 etc) to record certain events every day of the
month. Some days may be empty and have nothing written.

There is then another file which I would like to fill automatically from
this series of files per month. This new file, called Summary in which i have
5 sheets (a,b,c,d,e). I would like to find a way of automatically copying
data

from - Jan/1 cells d12, e12 & m12
to - Summary/a cells f2, g2 & d2 (row 2 to is for 1 Jan)

The next day, Jan 2 i need to take the data from the same file (if its the
same month) but a different sheet, Jan/2 cells d12, e12 & m12 and copy to
summary/a cells f3, g3 & d3 (row 3 is for 2 Jan). Alternatively if its the 31
Jan it copies from Jan/31, the next day it needs to find a different file and
sheet, Feb/1.

So basically I need a way of searching through a network by date to take
data from a certain folder and sheet to copy into a corresponding row by date
in another folder. The only static items are the cells to copy from, (source
file and sheet change) and the file name and sheet to paste to (cells to
paste to increase by one per day.

I have looked at Ron de Bruins site but come up blank so far.

Is this possible?

Thanks
LiAD