You'll get error 9 if the worksheet is not called Master. Maybe you have
extra spaces in the worksheet name? You can change the line below to match
your real worksheetname.

Worksheets("Master").Activate

"smonsmo" wrote:

Thanks Joel,
I copied this code and made the needed changes to the directory but when it
runs I end up with this error. Runtime error 9 subscript out of range. I
does open each file but does not copy any info.

Any ideas?

"Joel" wrote:

Try this code. Change client folder to the folder containing the workbooks
you want to get a summary from. The workbook that yo place this macro
should not be in the same directory.

Sub gettotals()

Const Clientfolder = "c:\temp\test"

RowCount = 1
first = True
Do
If first = True Then
Filename = Dir(Clientfolder & "\*.xls")
first = False
Else
Filename = Dir()
End If

If Filename < "" Then
Workbooks.Open (Clientfolder & "\" & Filename)
Worksheets("Master").Activate

Range("U3").Copy Destination:= _
ThisWorkbook.ActiveSheet.Range("A" & RowCount)

Range("W3").Copy Destination:= _
ThisWorkbook.ActiveSheet.Range("B" & RowCount)

Workbooks(Filename).Close

RowCount = RowCount + 1
End If

Loop While Filename < ""

End Sub


"smonsmo" wrote:

I have one folder that contains several workbooks. They are contracts and
have been saved to this folder based on their scheduled date for the work to
be completed. (master!u3) Their names will be random. (Customer names last
name, first name) I would like to extract the value found at Masterw30 which
is the dollar total for the job from each workbook found in this folder.
Then compile a list for each week of the month based on each workbooks
scheduled date. This way I will be able to see the amount of work we have
scheduled each week.

Thanks