Using Excel to copy and paste one of 9 different replies

[ellison]

Hi, wondering if it's possible to automate something like the below:

When getting back to people, we've got a number of different main messages that we get back to people with. We've boiled down the meat of the message (which then gets personnalised) and added them onto a spreadsheet.

At the moment we have to
i) click into the right message
ii) hit ctrl-A to select all of the text, doing it this way copies the "carriage returns" (is that the right term for the text starting on a new line?!)
iii) hit hit Ctlr C to copy it all to clipboard including the carriage returns
iv) lastly, hit Ctlrl V to paste

What we are wondering is if if there is a way of somehow having a sheet set up where clicking "1" would select the whole contents of Reply-1 (i.e. steps 1, 2 & 3 above)

Please note that if we just Ctrl-C the message, it copies in the quotation marks as well (which leads to extra steps of deleting them before sending).

I've put an example below, hope this helps

Just FYI, there is a different number of characters in each different type of reply. And some replies have only 2 carriage returns, but some have more.

excel-copy-different-bits-of-text-01-question.xlsm
	A	B
1	REPLY NUMBER	TEXT OF REPLY TO COPY
2	1	R1- Yes that is absolutely correct. Our team will be in touch tomorrow to confirm receipt. Many thanks
3	2	R2 - Following on from the conversation earlier, it is looking highly likely that the information is correct. Please bear with us during this stage whilst we check with the other department. Your patience is appreciated. Many thanks
4	3	Sorry to say that following on from the conversation earlier, it is does not look very lkely that the information looks correct. That said, please bear with us whilst we check the last details. If this matter is urgent, please contact us when you asap. Your patience is appreciated & you will have reply back within 1 working day with final decision. Kind Regards
5	4	Many thanks for your feedback. In order that we can progress your enquiry, we will need the following information. Please would you send copies of: 1- 2- 3- Warm Regards
6	5	With apologies, the Team have been dealing with unsually high demand. Your enquiry will be dealt with within 2 working days. Please can we apologise once again.
7	6	With apologies, the Team have confirmed our earlier findings. It will not be possible to progress this matter at this moment. Could we please suggest that you revisit this matter with us in Q1? Warm Regards
8	7	With apologies, the Team have confirmed our earlier findings. It will not be possible to progress this matter. Please can we thank your your time and wish you luck going forwards. Best Regards
9	8	Backup-1A Backup-1B
10	9	Backup-2A Backup-2B Backup-2C
Sheet1

 

[Engberg]

Here is one solution with a VBA macro. You could trigger this is many ways; with buttons (like in the attached GIF), with a CTRL or CTRL+SHIFT shortcut of your choosing or by clicking the cells like you described.

Function Clipboard$(Optional s$)
    Dim v: v = s  'Cast to variant for 64-bit VBA support
    With CreateObject("htmlfile")
    With .parentWindow.clipboardData
        Select Case True
            Case Len(s): .setData "text", v
            Case Else:   Clipboard = .GetData("text")
        End Select
    End With
    End With
End Function

Sub copyWithoutQuotes()
Clipboard (ActiveCell.Value)
End Sub

 

[ellison]

Hi, sorry - thick-o here! So far I've installed it...

I've managed to get the code working, but I don't think I'm triggering it right. Only way so far is:
Select cell, Alt+F8, Run, Ctrl+Shift, Ctrl-V

Wondering if I'm meant to create the buttons (as per screenshot) or create the shortcut commands maybe?
Is there a way of cutting down on these steps at all?

Many thanks

 

[Engberg]

Sorry, I wasn't very clear in my answer. The code I posted is for the keyboard shortcut option. If you want buttons, you'd have to create them and hard code which cell to copy from on the buttons, or with the code I posted go to Developer tab > Macros > select "copyWithoutQuotes" > More/Alternatives or whatever it says in your language > set a shortcut (lower case letter will be a CTRL shortcut, UPPER CASE LETTER will result in a SHIFT CTRL shortcut). If you want to go with the "click in cell A1 to copy text without quotes in B1" I could set that up for you as well.

 

[ellison]

Hi, you are explaining it very well - it is me that is catching up too slowly!

The Ctrl+q is really helpful, thanks. So I think now the keyboard shortcut method is: Select cell, ctrl+q, ctrl+shift, ctrl+V

I think what will slow down the people using it is having to run through those 3 steps: i) select the cell ii) hitting the ctrl+q iii) ctrl+shift

Is there a way for example that something like this could work?
Ctrl+shift + 1 copies the contents of cell B2 (without the quotes)
Ctrl+shift + 2 copies the contents of cell B3 (without the quotes)
Ctrl+shift + 3 copies the contents of cell B4 (without the quotes)
etc etc

And maybe if it isn't Ctrl+shift, then something else?

 

[Engberg]

What is Ctrl + Shift? If you connected copyWithoutQuotes to Ctrl+Q (or other shortcut) the steps should be:

1. Select cell to copy
2. Ctrl+Q
3. Ctrl+V

You can only connect a letter to the Ctrl / Ctrl + Shift shortkeys, so 1-9 not possible. If you don't like the Ctrl + Q to copy (or other letter) I could also set it up so that just clicking A1 (or even B1) copies the text in B1 like you described in the original post.

 

[ellison]

Hi, now that I've finally understood the steps (sorry - that's my fault!) that is a GREAT solution, thanks very much.

Buttons to click would also be great, but honestly you've done plenty enough for us - thanks

Wil mark this up as the solution

Huge thanks again