cannot get total
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
|