"#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.

Please Login or Register  to view this content.

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.