Macro to Copy/Paste 'as text'

I want to copy the contents of a given cell and paste them ‘as text’ into another empty cell, as copy/pasting regularly will simply paste the formula, which is not what I’m after.

I’m new to using Macros, and due to this fact have been using the Macro Recorder to achieve the creation of simple ones.
However I’ve run into an issue: the macro recorder only records simple actions; it won’t detect a copy/paste ‘as text’, only a simple copy/paste.

I intend to familiarise myself further with Macro creation, however before I do that I wonder if someone could help me with a workaround for the layperson, be it coding a simple macro to achieve the task, or otherwise.

Have attached the document in question.
In the document I want the ‘Result’ text (cell E2 of ‘Initial Exercise - Convention 1’ sheet) copied to each empty cell in column J, each instance of copying being activated by the adjacent ‘attempt’ macro button.
This is done in order to maintain a record of correct/incorrect answers.


Copy Results (1).ods (33.1 KB)

If you pasted a formula, the content of the source cell was the formula.
Other cells may have a text or a number as their content.
Having copied any cell you can always paste its result without a probably present formula using Ctrl+Shift+V (>Edit>Paste Special…).
Mainly concerning numbers you need to additionally decide if you want to also apply the NumberFormat used for the display in the source cell. You may think of that diplay as a “text”, but it isn’t.
If you actually have a number as content or as a formula result, and definitely want to paste it as a text, this requires a conversion regarding a NumberFormat. To do this, you should probably enter a conversion formula into the target cell like
=TEXT(SourceCellReference; NumberFormatString)
To make the result persistent independent of changes in the source cell, you can subsquently Copy/PasteSpecial that result in the target cell.
A “macro” for the purpose should be avoided with the exception of the case that you need a specific proceeding extremely often, and either know how to write macros, or are contented witth a recorded macro.

1 Like

Your Copy Results.ods is blank.

In addition to @Lupp above, you can add a Paste unformatted text icon to a toolbar:

  • Click Tools > Customise, in the dialogue that opens, select the Toolbars tab
  • In the right pane Assigned commands select the icon you want the new icon to appear next to.
  • In the left pane of Available Commands select Paste Unformatted Text and click the right-facing arrow to add it to the Assigned Commands pane. Click OK.

If you click on the commands in the left pane, the macro command will appear in the description field, hover for it as a tooltip.

1 Like

Sorry about the document. Have updated-any luck this time?
Downloading it after upload elicited a populated document for me…

Have looked into the icon you mentioned. A useful tip, but certainly not as helpful as a Macro in this instance; cell E2 must be selected, copied, then the paste position must be selected, before the icon can finally be clicked to complete the process. As opposed to simply clicking the relevant ‘attempt’ macro button.

Considering this document may be used an indefinite number of times, this will likely become increasingly irksome with time.