VBA to Copy Cell Value Without Formula
 

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

VBA to Copy Cell Value Without Formula
 

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

--
Gary''s Student


"JohnnyTheAmmer" wrote:

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

VBA to Copy Cell Value Without Formula
 

Hi

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

JTH

"Gary''s Student" wrote:

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

--
Gary''s Student


"JohnnyTheAmmer" wrote:

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

VBA to Copy Cell Value Without Formula
 

Johnny,

You could use PasteSpecial:Values.

However, I think you could use the formula:

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

HTH

"JohnnyTheAmmer" wrote:

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