Create Function to Copy with Format

[THC]

Hello,

In an Excel sheet I have texts with special format (to insert later in a word document, I use the Excel sheet as a database).
If I copy/paste the cell I keep the format but I need to use a formula to select in each case which text to use (I use a Xlookup) and If I do so, I lose the format.
I tried to make a VBA function to copy with format and mix the function with the xlookup to bring back the selected text wit format.
First attempt:

Public Function Copy_with_Format(copiedCell As Range) As String
           Copy_with_Format = copiedCell.Value
End Function

In an Excel cell I type "=Copy_with_Format(", I select the cell I want to copy and ")"...
It works but copy without the format (like the formula "=").

Then I tried to use ".PasteSpecial":

Public Function Copy_with_Format(ByVal copiedCell As Range) As String
    copiedCell.Copy
    Copy_with_Format = copiedCell.PasteSpecial(xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False)
    Copy_with_Format = copiedCell.PasteSpecial(xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False)
End Function

The only result I obtain is "False"...
Does anyone have an idea to make this function running?

Thank you in advance,

THC (on Office 365)

 

[Fluff]

Hi & welcome to MrExcel.
When calling a function from the sheet, it cannot copy formats.
A UDF has the same limitations as a normal formula.

 

[THC]

Thank you for this very quick answer.
I'm surprised because the simple copy/paste "do the work" and I thought it would be possible to "automatize" this in a function.
There is really no way to "turn around" this limitation?
For example copying to another sheet, copying back to the first one and erase the temp cell?

BR
THC

 

[Fluff]

You can do it with a macro, but not when calling the function from the sheet.

 

[THC]

Thank you, knowing there was no solution, I stopped loosing time and found an alternative solution:
The xlookup "brings back" cell's reference and the VBA insert directly the cell's content in Word (with format)

I hope I'm clear, problem solved.
Thanks again

THC

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[THC]

You're welcome, thanks to you.