I have a procedure where from a workbook I select and open another
workbook and copy data values from that book (source) to the other
workbook ( target). Part of the procedure is shown below,



With targetfileEntries
.Range("A17:B161").Value = sourcefileEntries.Range("A12:B156").Value
.Range("D17161").Value = sourcefileEntries.Range("F12:F156").Value
.Range("E17:E161").Value =
sourcefileEntries.Range("E12:E156").Value
End With

What I would like to do is have a column in the target file i.e
Range("H17:H161").Value and I want it to be have the sum of the values
from the two columns

sourcefileEntries.Range("G12:H156").Value so that in the target file
for eaxmple Cell H17 has the value of the two cells in the source file
G12 plus H12.

I hope this is understandable as I wouls be most grateful for any help
and advice.



Kind Regards,



Graham
January 6, 2012 Reply with quote Report abuse
Child exploitation or abuseHarassment or threatsInappropriate/Adult
contentNudityProfanitySoftware piracySPAM/AdvertisingVirus/Spyware/
Malware dangerOther Term of Use or Code of Conduct violation 1 Person
had
this question Stop emails Graham62

Graham Haughs wrote:

> I have a procedure where from a workbook I select and open another
> workbook and copy data values from that book (source) to the other
> workbook ( target). Part of the procedure is shown below,
>
>
>
> With targetfileEntries
> .Range("A17:B161").Value = sourcefileEntries.Range("A12:B156").Value
> .Range("D17161").Value = sourcefileEntries.Range("F12:F156").Value
> .Range("E17:E161").Value =
> sourcefileEntries.Range("E12:E156").Value
> End With
>
> What I would like to do is have a column in the target file i.e
> Range("H17:H161").Value and I want it to be have the sum of the values
> from the two columns
>
> sourcefileEntries.Range("G12:H156").Value so that in the target file
> for eaxmple Cell H17 has the value of the two cells in the source file
> G12 plus H12.
>
> I hope this is understandable as I wouls be most grateful for any help
> and advice.

A few ways to do this. The simplest is to just add the source data:
With targetfileEntries
For L0 = 17 To 161
.Cells(L0, 8).Value = sourcefileEntries.Cells(L0 - 5, 7).Value + _
sourcefileEntries.Cells(L0 - 5, 8).Value
Next
End With

If the sums need to update with the source data, you could instead do this:
With targetfileEntries
For L0 = 17 To 161
.Cells(L0, 8).Formula = "=SUM([test1.xlsx]Sheet1!$G" & (L0 - 5) & _
":$H" & (L0 - 5) & ")"
Next
End With
....with "[test1.xlsx]Sheet1" replaced with the correct names. (If those
aren't know until runtime, try using sourcefileEntries.Parent.Name and
sourcefileEntries.Name.)

--
I'm on an epic quest to find the 8th day of the week.