ThisWorkbook.Worksheets(1).Cells(iCol, 6).Value = _
.Range("F45").Copy

Will put the value of F45 in your worksheet.

Cells(row, col)

why use a variable named icol to refer to the row?

Regards,
Tom Ogilvy


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