Hi Mike,

Here's a first shot:

I have files called "callsxx.xls" where xx is the week number. these
files reside in a folder "c:\Excel_tests\Calls\". Both the path and the
file names can be set in the procedure below.

Then I have a consolidation workbook with a combobox in B1 which links
into B1 and is populated with the names of the week days from a table
on sheet "Sheet3".
In E1 I have the number of the weekday corresponding to the name chosen
from the combobox.

Formula is: =OFFSET(Sheet3!A1,MATCH(B1,Sheet3!A1:A7,0)-1,1)

The table looks like this:

Monday 1
Tuesday 2
Wednesday 3
Thursday 4
Friday 5
Saturday 6
Sunday 7

Row 3 has the headers "Store" and the 52 weeks.

Rows 4 to n will be populated from the "calls" files by the VBA
procedure.

At the top I have a command button "Consolidate" which holds the code
of the VBA procedure.

After consolidation all rows are sorted in ascending order of store
name.

The design of the "calls" files is like this:

week end 26.03.2006 13
Store Monday Tuesday Wednesday Thursday Friday Saturday Sunday
B 12 33 4 22
C 45 20 45
L 55 12 88 14 20
X 40

D1 contains the week number corresponding to the week end date. Formula
is: =WEEKNUM(C1,2)

If you write me an email with or without your workbook files I can send
you the test files if you like.

Here's the VBA procedu

Private Sub CommandButton1_Click()

'Set the search path and the file name to search for

SearchPath = "C:\Excel_tests\Calls"
FileToSearch = "call*.xls"

'determine the number of rows occupied in the consolidate worksheet

AnzConsA =
Workbooks("Consolidate.xls").Worksheets(1).Cells(R ows.Count,
"a").End(xlUp).Row

'clear the consolidate worksheet

Workbooks("Consolidate.xls").Worksheets(1).Range(" 4:" +
CStr(AnzConsA)).Clear

'get the names of the files in search path tha meat the file name

With Application.FileSearch

.NewSearch
.LookIn = SearchPath
.SearchSubFolders = False
.Filename = FileToSearch
.MatchTextExactly = True
.FileType = msoFileTypeExcelWorkbooks

If .Execute() < 1 Then
MsgBox ("There were no files found.")
Exit Sub
End If


'loop through the files found in search path

For i = 1 To .FoundFiles.Count
ff = .FoundFiles(i)
Workbooks.Open ff
fn = ActiveWorkbook.Name

'determine the number of rows occupied and the week number
of the file just opened

anzColA = Workbooks(fn).Worksheets(1).Cells(Rows.Count,
"a").End(xlUp).Row
WeekNum = Mid(fn, 5, 2)

'loop through the rows in the current file

For k = 3 To anzColA
Set cls = Workbooks(fn).Worksheets(1).Range("a" +
CStr(k))
AnzConsA =
Workbooks("Consolidate.xls").Worksheets(1).Cells(R ows.Count,
"a").End(xlUp).Row + 1

'with the current store name loop through the rows in
the consolidate worksheet

For m = 4 To AnzConsA
Set cons =
Workbooks("Consolidate.xls").Worksheets(1).Range(" a" + CStr(m))
If cons.Value = "" Or cons.Value = cls.Value Then

'if store was found add the sales figure to the
corresponding week
'and select only the day selected on the
consolidate worksheet

SelDay =
Workbooks("Consolidate.xls").Worksheets(1).Range(" e1").Value
cons.Value = cls.Value
cons.Offset(0, WeekNum) = cons.Offset(0,
WeekNum) + cls.Offset(0, SelDay).Value
GoTo NextEntry
End If
Next m
NextEntry:
Next k

'close the current workbook

Workbooks(fn).Close SaveChanges:=False
Next i
End With

'Sort the consolidation rows

AnzConsA =
Workbooks("Consolidate.xls").Worksheets(1).Cells(R ows.Count,
"a").End(xlUp).Row + 1
Set cons = Workbooks("Consolidate.xls").Worksheets(1).Range(" 4:" +
CStr(AnzConsA))

'cons.Select
cons.Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


End Sub

Hope it works at your end. It does here. :-)

Regards

Hans