Macro, want to get values from a cell without changing sheets

[D3Pratt]

Made a macro to Copy a value and paste is as "Special" for Value only.

The problem is it won't function correctly Unless I force a switch to the sheet where the calculations are. (Which I want to avoid)

The current code is:

Please Login or Register  to view this content.

Is there a way to do this without switching sheets as that page will be hidden?

[shg]

Please take a few minutes to read the forum rules about thread titles, and then amend yours accordingly.

[added by pjoaquin]: Also, please remember to post your questions in the proper forum. I've moved this thread to the Programming forum this time.

[Paul]

Thanks for updating your title, D.

Are you simply trying to copy the value in Calc!C10 to Calc!D10?

Rather than using Select statements (almost always unnecessary), or copying/pasting, just set the value of D10 equal to C10. For example:

Please Login or Register  to view this content.

Hope that helps.

[mudraker]

No need to select sheets or ranges
No need to copy & paste

To copy value between sheets

Please Login or Register  to view this content.

To copy value on same sheet

Please Login or Register  to view this content.

[D3Pratt]

The specifics of what I'm trying to do is this:
(And since I'm a noob I'm probably doing it all backwards)

On the UserForm I have 2 buttons:

First one writes a Now() to Calc!C8
Second one writes a Now() to Calc!C9
Calc!C10 the subtracts 8 from 9 to get an elapsed time.

When I set ControlSource of TextBoxDisplay to Calc!F4 it would overwrite the formula and effectively break the function

So, I did a macro to Copy and paste the Value only in a cell next to the formula, but that macro now HAS to have the Sheet open that will eventually be hidden.

I'm working on a new workaround and linking that cell to the main page and will Hide it somehow, but it's clunky, I don't like it.

[D3Pratt]

No need to select sheets or ranges
No need to copy & paste

To copy value between sheets

Please Login or Register  to view this content.

To copy value on same sheet

Please Login or Register  to view this content.

That did it, but I had to swap the cell reference:

Please Login or Register  to view this content.

Seems that with C10 in front it started off by overwriting the formula with the contents of an empty cell.

Thank you greatly!