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

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

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

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