VBA to Copy Cell Value Without Formula

[Guest]

Hi

I have 2 workbooks, 1 contains a sheet that has a formula in cell C3 and C7,
this does a countif if function to give me figures, the other workbook
contains data for a whole year, in this book I have a cell that requires the
sum total of both cells from the 1st workbook, i.e C3 = 3, C7=7 so cell C3 in
the other workbook needs to read 10, I have tried using a seperate cell to do
the calculation and then copy the total across but it just gives me 'REF#'
Is there a way of copying accross without taking the Formula with it, ie
just the number in the cell???

Cheers

JTH

 

[Guest]

How about:
Sub Macro1()
Dim r1, r2, r3 As Range
Set r1 = Workbooks("Book1").Worksheets("Sheet1").Cells(3, 3)
Set r2 = Workbooks("Book1").Worksheets("Sheet1").Cells(3, 7)
Set r3 = Workbooks("Book2").Worksheets("Sheet1").Cells(3, 3)
r3.Value = r2.Value + r1.Value
End Sub

If all you need is the value, you don't need Copy/Paste

 

[Guest]

Hi

Thanks for the prompt reply, I will give that a go and let you know if it
works for me

JTH

 

[Guest]

Johnny,

You could use PasteSpecial:Values.

However, I think you could use the formula:

='FullPath[FileName]Sheet1'!C3+'FullPath[FileName]Sheet1'!C7

HTH