"#value" appears after pasting value of formula between spreadsheet

[Ricaz]

Hi there!
First time I humbly ask a question in this forum, and I thank you in advance for your help & attention.

I am copying data from a spreadsheet and pasting it into another spreadsheet.
The data I am copying/pasting is actually the value of formulas.

Sounds pretty simple: in the original spreadsheet I select the cell I need, hit ctrl+c, switch to the other spreadsheet, hit ctrl+v, hit ctrl again, and hit "v" to mean "value"

However, this gives me an odd problem with a specific formula.
I copy the cell, and everything is all right, switch to the other spreadsheet (still everything ok), but when I paste the content (note: in the second spreadsheet) there is a disaster in the original spreadsheet: the formula suddenly stops working, gives the dreaded "#value" error, and excel tells me that "a value used in the formula is of the wrong data type". This has a domino effect in all following sheets of the original spreadsheet. Apocalypse.

Actually the same effect happens when I paste anything (from any source) in the OTHER WORKBOOK. Or even type in it... I am in tears. If I paste or type in the original workbook, everything seems fine.

Here follow some details which I think will be the key of the thing...

The cell that creates problems contains a formula that tells excel to look into the same cell of the previous sheet, add one to its content, and show the sum.
=1+(prevsheet(D23))

Prevsheet() is a custom-made function, that I have created with VBA (or rather copied from a guy on the internet, let's try to be honest).
This is the VBA code of the prevsheet() function.

Function PrevSheet(rCell As Range)
    Application.Volatile
    Dim i As Integer
    i = rCell.Cells(1).Parent.Index
    PrevSheet = Sheets(i - 1).Range(rCell.Address)
End Function

Thanks in advance for you help.


Ricaz

[Bernie Deitrick]

Hit ctrl+c, switch to the other spreadsheet, select the destination cell, right-click, and choose the "123" clipboard icon, which pastes values.

[Ricaz]

Hi Bernie, thanks for the reply.
your suggestion is certainly correct, in a normal scenario, and I had a "more-keyboard-friendly" alternative already in my original post ("I select the cell I need, hit ctrl+c, switch to the other spreadsheet, hit ctrl+v, hit ctrl again, and hit "v" to mean "value").
The real problem comes afterwards, when the formula with the custom function stops working.

Further tests suggest that: if the spreadsheet with the formula with the custom function is open, the formula/function stops working (showing #value) if I type or paste anything in ANOTHER spreadsheet. Probably it is a global thing with VBA.