cannot get total
 

thanks Guys,

it worked a treat.
you have no idea how long i have tried other things only to find a really
simple fix.

once again thank you to you both

Regards,

Scott

"Scott" wrote in message
...
Hi There,

i have the following code (see below) but i cannot get the total or the
balance of each spreadsheet.
you see F45 is the result of adding F41+F43+F44, so by copying the
destination i will only get a #ref
any assistance is greatly appreciated

Sub SubGetMyData()
Application.ScreenUpdating = False

Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objSubfolder As Scripting.Folder
Dim objFile As Scripting.File
Dim iCol As Long

iCol = 3
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("e:\scott\blah\")
For Each objFile In objFolder.Files
If objFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=objFolder.Path & "\" & objFile.Name
With ActiveWorkbook.Worksheets(1)
.Range("A13").Copy
Destination:=ThisWorkbook.Worksheets(1).Cells(iCol , 1)
.Range("A14").Copy
Destination:=ThisWorkbook.Worksheets(1).Cells(iCol , 2)
.Range("A15").Copy
Destination:=ThisWorkbook.Worksheets(1).Cells(iCol , 3)
.Range("F7").Copy
Destination:=ThisWorkbook.Worksheets(1).Cells(iCol , 4)
.Range("F8").Copy
Destination:=ThisWorkbook.Worksheets(1).Cells(iCol , 5)
.Range("F45").Copy
Destination:=ThisWorkbook.Worksheets(1).Cells(iCol , 6)
End With
ActiveWorkbook.Close savechanges:=False
iCol = iCol + 1
End If
Next

Application.ScreenUpdating = True
End Sub