Build Paste Special Value Function

[GeoKoro13]

Hi there,

I'm new here and just recently try to use VBA to make my life a bit easier. But still I'm away in making a code by myself.

What I'm trying to do at the moment is to build a function to copy-paste special the value only. I've seen people posting how to copy and paste special from specific cells, but it would make my job much easier if I could just typing =pastespecial(...).

Any idea if that could happened?

Regards

 

[tygrrboi]

Do you mean typing .PasteSpecial into VBA?

the syntax is like this

.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)

for the first part, Paste, you can choose any of the following

xlPasteAll	Everything will be pasted.
xlPasteAllExceptBorders	Everything except borders will be pasted.
xlPasteAllMergingConditionalFormats	Everything will be pasted and conditional formats will be merged.
xlPasteAllUsingSourceTheme	Everything will be pasted using the source theme.
xlPasteColumnWidths	Copied column width is pasted.
xlPasteComments	Comments are pasted.
xlPasteFormats	Copied source format is pasted.
xlPasteFormulas	Formulas are pasted.
xlPasteFormulasAndNumberFormats	Formulas and Number formats are pasted.
xlPasteValidation	Validations are pasted.
xlPasteValues	Values are pasted.
xlPasteValuesAndNumberFormats	Values and Number formats are pasted.

<tbody>
</tbody>

for the second part, Operation, you can include these (provided that you are pasting numeric data and not a formula or a format or text)

xlPasteSpecialOperationAdd	Copied data will be added with the value in the destination cell.
xlPasteSpecialOperationDivide	Copied data will be divided with the value in the destination cell.
xlPasteSpecialOperationMultiply	Copied data will be multiplied with the value in the destination cell.
xlPasteSpecialOperationNone	No calculation will be done in the paste operation.
xlPasteSpecialOperationSubtract	Copied data will be subtracted with the value in the destination cell.

<tbody>
</tbody>

Leave this blank if you do not want an operation performed.

SkipBlanks, and Transpose are True or False values. If left blank they will by default be false, but if you would like blanks skipped, or for the data to be transposed, set the appropriate one to True

 

[Norie]

User defined functions can only return values to the cell(s) they are in.

They can't be used to put/change values in other cells which is what you appear to want to do.

 

[Kenneth Hobson]

Welcome to the forum!

User Defined Functions (UDFs) return values and not perform what Subs can do.

You could use a Worksheet event and enter some value to cause the action. It would be better to just add a hot key combination to a Sub that acts on the activecell to do it.

 

[GeoKoro13]

Thank you for the quick response.

Well, I mean building a formula that can be used in a spreadsheet that will paste the value from another cell.
So, for instance I will type in a cell ="function name"(A1) and will permanently paste the value. The problem is that I don't want to have a specified range of cells to paste their values but instead I would like to use a custom formula that will do that for me.

Does that make sense?

 

[GeoKoro13]

You guys are pretty quick! Before I replied to the first response I got 2 more

 

[GeoKoro13]

So, it's not possible to use a UDF to call a macro that will paste special a value for the cell I'm interested for?

 

[Kenneth Hobson]

No, not to Microsoft as we explained. https://support.microsoft.com/en-us/kb/170787

 

[tygrrboi]

What kind of paste special are you looking to do? Perhaps your goal can be achieved via another method (different formulas for example or the format painter...)

 

[My Aswer Is This]

Why would a dynamic Macro not work for you. Explain a little more and give an example of what your wanting to do.