My code-can't get it work right

[kluitna]

here is my code:

Sub test()
Dim Source As String
Dim Path As String
Dim NumSheets As Long
Dim SheetName As String
Path = Worksheets("List").Cells(1, 1)
For i = 0 To 500
Source = Worksheets("list").Cells(2 + i, 1)
If Source = blank Then End
ChDir Path
Workbooks.Open FileName:=Source
For h = 1 To 30
Windows("Estimate Summary.xls").Activate
Worksheets("ITEMS").Select
Worksheets("ITEMS").Cells(6 + i, 1 + h).Select
strfile = Source
SheetName = Workbooks(Source).Worksheets(h).Name
ActiveCell.FormulaR1C1 = "='" & [strfile] & "'!R21C4"
Workbooks.Open (Source)
ActiveSheet.Cells(21, 4).Select
Selection.Copy
Windows("Estimate Summary.xls").Activate
Worksheets("ITEMS").Cells(6 + i, 1 + h).Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Next h
Next
End Sub

Its works sort of.. 2 problems,

1) the formula that looks back at the souce file to link to the estimate summary I can not make it roll through the diffeent sheets in a given source file. does anyone out there know the code to do this?

2) i have been trying to figure out how to close the source file before. opening a new one and rolling though the sheets in the new file.

my fore head is bloody for some constant head banging.. in need of a medic!!!

 

[Anonymousity]

Perhaps a solution to a part of the problems at :-

http://www.msf.org/countries/index.cfm