Function not copying

[ppsza]

For a variety of solid reasons that are too lengthy and irrelevant t
get into here, I have the following setup: a cell that contains th
following formula

=GetTheText("D95", "D49"

(D95 and D49 are just examples. What is important is that the cel
that contains this formula is neither cell D95 or D49.

Here is the GetTheText function

Function GetTheText(SourceCell As String, TargetCell As String

Range(SourceCell).Selec
Selection.Cop
Range(TargetCell).Selec
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone
SkipBlanks:=
False, Transpose:=Fals

End Functio

Nothing seems to happen. When I check the clipboard it shows nothin
in there. Is there any reason why a function might refuse to put
value into another cell? Seems unlikely.

The bigger question here is how can create a function (so that I ca
plug it into a cell) that copies the contents of a cell and paste
special (to retain formatting) into another cell. We're talking
cells, here

thank you

 

[Steve Garman]

Worksheet functions will not do that, which is what Juan Pablo González
was trying to tell you last time you asked.

http://www.google.com/[email protected]

 

[Melanie Breden]

For a variety of solid reasons that are too lengthy and irrelevant to

get into here, I have the following setup: a cell that contains the
following formula:

=GetTheText("D95", "D49")

(D95 and D49 are just examples. What is important is that the cell
that contains this formula is neither cell D95 or D49.)

Here is the GetTheText function:

Function GetTheText(SourceCell As String, TargetCell As String)

Range(SourceCell).Select
Selection.Copy
Range(TargetCell).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

End Function

Nothing seems to happen. When I check the clipboard it shows nothing
in there. Is there any reason why a function might refuse to put a
value into another cell? Seems unlikely.

The bigger question here is how can create a function (so that I can
plug it into a cell) that copies the contents of a cell and pastes
special (to retain formatting) into another cell. We're talking 3
cells, here.

Sheet-Functions can only return a result to the respective formula cell
who called the function.
It is not possible to changed other cells with sheet functions.
You have to use a Sub procedure:

Sub CallGetTheText()
GetTheText "D95", "D49"
End Sub

--
Regards
Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)