I want to copy multiple cells as one sentence to paste in another program

[Limeskin]

Hey,

I'm looking to create a table with pre-determined sentences in, so that all I need to do is add a person's name in the middle cell, and hit copy on a command button, and I will have copied the multiple cells, to be able to copy as one sentence in another program elsewhere.

I have found a code that lets me use VBA macros (sorry I'm new to all this so my language may be incorrectly used here). It seems to work, and I have posted this below:

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim xSheet As Worksheet
Set xSheet = ActiveSheet
If xSheet.Name <> "Definitions" And xSheet.Name <> "fx" And xSheet.Name <> "Needs" Then
xSheet.Range("A1:C17 ").Copy
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If

Application.ScreenUpdating = True
End Sub

It's handy as it lets me just change the cell range and I am good to go.

How do I stop it from copying the formatting? If I made a range of say A1:C1, I find that what ever is written into B1 has large gaps between it, where as I want it to read as a sentence with no large gaps, just one line. (cells A1 and C1 will have fixed text in them that I plan to lock, so that all I need to do is add a name into B1 and hit the command button to copy everything as one sentence. This is to save me writing out the same sentence for different people over and over again).

Can anyone help? It's not a massive issue but I'm trying to fine tune what I'm looking for so that it doesn't have big gaps. Just everything as one text.

Thanks.
Lime

 

[Micron]

I interpret that as follows:
you need to loop over every cell in the range and concatenate its value to a string variable and do something with the result. However, that doesn't take into account that the end of one cell value and the beginning of the next won't be separated unless you insert something in between (like a space or line feed/carriage return).

I guess I don't completely understand though. If it's the same sentence repeatedly, why not just enter it once somewhere? If that is because a name changes every time, then just use a place holder in the sentence and use Replace function to swap it with the name.

 

[Limeskin]

I interpret that as follows:
you need to loop over every cell in the range and concatenate its value to a string variable and do something with the result. However, that doesn't take into account that the end of one cell value and the beginning of the next won't be separated unless you insert something in between (like a space or line feed/carriage return).

I guess I don't completely understand though. If it's the same sentence repeatedly, why not just enter it once somewhere? If that is because a name changes every time, then just use a place holder in the sentence and use Replace function to swap it with the name.

Hey. As I feared, you've entered the conversation thread with the assumption I know what you're talking about... Variable and strings mean nothing to me unfortunately, so you'll have to rephrase all of that in laymen's terms thanks.

The issue I have is that when I use the VBA option and create a command button through macros, it seems to copy the formatting from Excel into the new program so it pastes it as a table. If you test the code I've sent over, you'll see that it should work, but when you copy your info using the command button, and then paste that into Word for example, it copies the table outlines and is very stretched out. This is not so much an issue as I am pasting it into an online information storage program, so need to paste it to a web browser not Word. I just find there are slight formatting issues from even here. There are gaps either side of the name and you can tell out has come from a table.

How do I copy multiple columns from a table as one singular sentence that combines the lot. It'd be A2:C2 (as an example) that I'm copying over, with A2 containing the beginning of the sentence, B2 containing the name I'll manually enter, and C2 with the end part of the conversation. I'd copy all 3 columns as one sentence, with no extended gaps between words.

Hope this helps.

 

[Micron]

OK, a variable is a placeholder for data (numbers, text and a few other 'thing's). A string is one or more characters to be thought of as 'text' (although even 123 can be treated as text). Concatenation is piecing together bits of strings. You seem to want to concatenate what's in C2 onto B2 and that result onto A2.
I did some testing with pasting to see if I could replicate your issue with pasting only text. To my surprise, I got spaces between words even when pasting into notepad, which is about the least format capable option I could think of. Surprise - big spaces. So I will try pasting a concatenated string and let you know the result.

 

[Limeskin]

Hey. As I feared, you've entered the conversation thread with the assumption I know what you're talking about... Variable and strings mean nothing to me unfortunately, so you'll have to rephrase all of that in laymen's terms thanks.

The issue I have is that when I use the VBA option and create a command button through macros, it seems to copy the formatting from Excel into the new program so it pastes it as a table. If you test the code I've sent over, you'll see that it should work, but when you copy your info using the command button, and then paste that into Word for example, it copies the table outlines and is very stretched out. This is not so much an issue as I am pasting it into an online information storage program, so need to paste it to a web browser not Word. I just find there are slight formatting issues from even here. There are gaps either side of the name and you can tell out has come from a table.

How do I copy multiple columns from a table as one singular sentence that combines the lot. It'd be A2:C2 (as an example) that I'm copying over, with A2 containing the beginning of the sentence, B2 containing the name I'll manually enter, and C2 with the end part of the conversation. I'd copy all 3 columns as one sentence, with no extended gaps between words.

Hope this helps.

Just additionally. The reason I'm not copying one sentence, is because it changes the bandaorey

OK, a variable is a placeholder for data (numbers, text and a few other 'thing's). A string is one or more characters to be thought of as 'text' (although even 123 can be treated as text). Concatenation is piecing together bits of strings. You seem to want to concatenate what's in C2 onto B2 and that result onto A2.
I did some testing with pasting to see if I could replicate your issue with pasting only text. To my surprise, I got spaces between words even when pasting into notepad, which is about the least format capable option I could think of. Surprise - big spaces. So I will try pasting a concatenated string and let you know the result.

Sorry to be difficult ? but thank you for the explanation; that helps. ??. I'm glad it's not necessarily down to my technical capabilities that it did work! Thank you for testing the concatenated string for me. I do greatly appreciate your support.

 

[Limeskin]

Didnt

Just additionally. The reason I'm not copying one sentence, is because it changes the bandaorey

Sorry to be difficult ? but thank you for the explanation; that helps. ??. I'm glad it's not necessarily down to my technical capabilities that it did work! Thank you for testing the concatenated string for me. I do greatly appreciate your support.

Didn't work*

 

[Micron]

If this doesn't work for you, either you'll have to be very specific about what you have to work with and what you want 'cause I'm a black & white kind of guy (I can see the trees but not the forest?). I was surprised at how difficult it is to get at the clipboard in VBA. Here's code with instructions for each code block:

Put this on the code module for the sheet:

Sub ConcatCells()
Dim ws As Worksheet
Dim strOut As String

Set ws = ThisWorkbook.Sheets("Sheet2") '<<change this to your sheet name
strOut = strOut & ws.Range("A1") & ws.Range("B1") & ws.Range("C1") 'make sure that this is your range of 3 cells
'Debug.Print strOut
ClipBoard_SetData strOut '<<this calls ClipBoard_SetData function and puts strOut on the clipboard

End Sub

Put this in a general module in your workbook (in vb editor, Insert Menu > Module) right under Option Explicit

#If VBA7 Then
  Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As LongPtr) As Long
  Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As LongPtr) As Long
  Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As LongPtr, ByVal dwBytes As LongPtr) As Long
  Declare PtrSafe Function CloseClipboard Lib "User32" () As Long
  Declare PtrSafe Function OpenClipboard Lib "User32" (ByVal hwnd As LongPtr) As Long
  Declare PtrSafe Function EmptyClipboard Lib "User32" () As Long
  Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long
  Declare PtrSafe Function SetClipboardData Lib "User32" (ByVal wFormat As LongPtr, ByVal hMem As LongPtr) As Long
#Else
  Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
  Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
  Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
  Declare Function CloseClipboard Lib "User32" () As Long
  Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) As Long
  Declare Function EmptyClipboard Lib "User32" () As Long
  Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long
  Declare Function SetClipboardData Lib "User32" (ByVal wFormat As Long, ByVal hMem As Long) As Long
#End If

Public Const GHND = &H42
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096

Public Function ClipBoard_SetData(sPutToClip As String) As Boolean

    ' www.msdn.microsoft.com/en-us/library/office/ff192913.aspx

    Dim hGlobalMemory As Long
    Dim lpGlobalMemory As Long
    Dim hClipMemory As Long
    Dim X As Long
    
    On Error GoTo ExitWithError_

    ' Allocate moveable global memory
    hGlobalMemory = GlobalAlloc(GHND, Len(sPutToClip) + 1)

    ' Lock the block to get a far pointer to this memory
    lpGlobalMemory = GlobalLock(hGlobalMemory)

    ' Copy the string to this global memory
    lpGlobalMemory = lstrcpy(lpGlobalMemory, sPutToClip)

    ' Unlock the memory
    If GlobalUnlock(hGlobalMemory) <> 0 Then
        MsgBox "Memory location could not be unlocked. Clipboard copy aborted", vbCritical, "API Clipboard Copy"
        GoTo ExitWithError_
    End If

    ' Open the Clipboard to copy data to
    If OpenClipboard(0&) = 0 Then
        MsgBox "Clipboard could not be opened. Copy aborted!", vbCritical, "API Clipboard Copy"
        GoTo ExitWithError_
    End If

    ' Clear the Clipboard
    X = EmptyClipboard()

    ' Copy the data to the Clipboard
    hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)
    ClipBoard_SetData = True
    
    If CloseClipboard() = 0 Then
        MsgBox "Clipboard could not be closed!", vbCritical, "API Clipboard Copy"
    End If
    Exit Function
ExitWithError_:
    On Error Resume Next
    If Err.Number > 0 Then MsgBox "Clipboard error: " & Err.Description, vbCritical, "API Clipboard Copy"
    ClipBoard_SetData = False

End Function

I ran Sub ConcatCells() then went to notepad and Word and pasted a single sentence without spaces or formatting.
If you want to read up on the long procedure I posted, it's here

How to copy strings to Clipboard using Excel VBA

The Microsoft Forms 2.0 Object library contains an object called DataObject that allows VBA developers to send (put) and read (get) text strings on the Windows clipboard. However, these methods seem...

www.spreadsheet1.com

HTH

 

[Limeskin]

If this doesn't work for you, either you'll have to be very specific about what you have to work with and what you want 'cause I'm a black & white kind of guy (I can see the trees but not the forest?). I was surprised at how difficult it is to get at the clipboard in VBA. Here's code with instructions for each code block:

Put this on the code module for the sheet:

Sub ConcatCells()
Dim ws As Worksheet
Dim strOut As String

Set ws = ThisWorkbook.Sheets("Sheet2") '<<change this to your sheet name
strOut = strOut & ws.Range("A1") & ws.Range("B1") & ws.Range("C1") 'make sure that this is your range of 3 cells
'Debug.Print strOut
ClipBoard_SetData strOut '<<this calls ClipBoard_SetData function and puts strOut on the clipboard

End Sub

Put this in a general module in your workbook (in vb editor, Insert Menu > Module) right under Option Explicit

#If VBA7 Then
  Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As LongPtr) As Long
  Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As LongPtr) As Long
  Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As LongPtr, ByVal dwBytes As LongPtr) As Long
  Declare PtrSafe Function CloseClipboard Lib "User32" () As Long
  Declare PtrSafe Function OpenClipboard Lib "User32" (ByVal hwnd As LongPtr) As Long
  Declare PtrSafe Function EmptyClipboard Lib "User32" () As Long
  Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long
  Declare PtrSafe Function SetClipboardData Lib "User32" (ByVal wFormat As LongPtr, ByVal hMem As LongPtr) As Long
#Else
  Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
  Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
  Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
  Declare Function CloseClipboard Lib "User32" () As Long
  Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) As Long
  Declare Function EmptyClipboard Lib "User32" () As Long
  Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long
  Declare Function SetClipboardData Lib "User32" (ByVal wFormat As Long, ByVal hMem As Long) As Long
#End If

Public Const GHND = &H42
Public Const CF_TEXT = 1
Public Const MAXSIZE = 4096

Public Function ClipBoard_SetData(sPutToClip As String) As Boolean

    ' www.msdn.microsoft.com/en-us/library/office/ff192913.aspx

    Dim hGlobalMemory As Long
    Dim lpGlobalMemory As Long
    Dim hClipMemory As Long
    Dim X As Long
   
    On Error GoTo ExitWithError_

    ' Allocate moveable global memory
    hGlobalMemory = GlobalAlloc(GHND, Len(sPutToClip) + 1)

    ' Lock the block to get a far pointer to this memory
    lpGlobalMemory = GlobalLock(hGlobalMemory)

    ' Copy the string to this global memory
    lpGlobalMemory = lstrcpy(lpGlobalMemory, sPutToClip)

    ' Unlock the memory
    If GlobalUnlock(hGlobalMemory) <> 0 Then
        MsgBox "Memory location could not be unlocked. Clipboard copy aborted", vbCritical, "API Clipboard Copy"
        GoTo ExitWithError_
    End If

    ' Open the Clipboard to copy data to
    If OpenClipboard(0&) = 0 Then
        MsgBox "Clipboard could not be opened. Copy aborted!", vbCritical, "API Clipboard Copy"
        GoTo ExitWithError_
    End If

    ' Clear the Clipboard
    X = EmptyClipboard()

    ' Copy the data to the Clipboard
    hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)
    ClipBoard_SetData = True
   
    If CloseClipboard() = 0 Then
        MsgBox "Clipboard could not be closed!", vbCritical, "API Clipboard Copy"
    End If
    Exit Function
ExitWithError_:
    On Error Resume Next
    If Err.Number > 0 Then MsgBox "Clipboard error: " & Err.Description, vbCritical, "API Clipboard Copy"
    ClipBoard_SetData = False

End Function

I ran Sub ConcatCells() then went to notepad and Word and pasted a single sentence without spaces or formatting.
If you want to read up on the long procedure I posted, it's here

How to copy strings to Clipboard using Excel VBA

The Microsoft Forms 2.0 Object library contains an object called DataObject that allows VBA developers to send (put) and read (get) text strings on the Windows clipboard. However, these methods seem...

www.spreadsheet1.com

HTH

This insane that you've had a go at this. I'm so thankful, honestly. I will actually give that a read like. I'll let you know how I get on!

Thanks again, Micron.

 

[Micron]

Forgot to mention you'll likely need a space at the end of the first string (cell value) and either after the name (2nd cell) or before the string in the cell(s) that follows.

 

[Jaafar Tribak]

The API declarations are not correct and the code won't work in x64bit excel.

Here is the correct code :

Option Explicit

#If VBA7 Then
    Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As LongPtr) As Long
    Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
    Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As LongPtr) As LongPtr
    Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
    Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
    Declare PtrSafe Function EmptyClipboard Lib "user32" () As Long
    Declare PtrSafe Function SetClipboardData Lib "user32" (ByVal wFormat As Long, ByVal hMem As LongPtr) As LongPtr
    Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As LongPtr
#Else
    Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
    Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
    Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
    Declare Function CloseClipboard Lib "user32" () As Long
    Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
    Declare Function EmptyClipboard Lib "user32" () As Long
    Declare Function SetClipboardData Lib "user32" (ByVal wFormat As Long, ByVal hMem As Long) As Long
    Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long
#End If

Const GHND = &H42
Const CF_TEXT = 1
Const MAXSIZE = 4096

Public Function ClipBoard_SetData(sPutToClip As String) As Boolean

    ' www.msdn.microsoft.com/en-us/library/office/ff192913.aspx
    #If Win64 Then
        Dim hGlobalMemory As LongLong, lpGlobalMemory As LongLong, hClipMemory As LongLong
    #Else
        Dim hGlobalMemory As Long, lpGlobalMemory As Long, hClipMemory As Long
    #End If
 
    Dim x As Long
 
    On Error GoTo ExitWithError_

    ' Allocate moveable global memory
    hGlobalMemory = GlobalAlloc(GHND, Len(sPutToClip) + 1)

    ' Lock the block to get a far pointer to this memory
    lpGlobalMemory = GlobalLock(hGlobalMemory)

    ' Copy the string to this global memory
    lpGlobalMemory = lstrcpy(lpGlobalMemory, sPutToClip)

    ' Unlock the memory
    If GlobalUnlock(hGlobalMemory) <> 0 Then
        MsgBox "Memory location could not be unlocked. Clipboard copy aborted", vbCritical, "API Clipboard Copy"
        GoTo ExitWithError_
    End If

    ' Open the Clipboard to copy data to
    If OpenClipboard(0&) = 0 Then
        MsgBox "Clipboard could not be opened. Copy aborted!", vbCritical, "API Clipboard Copy"
        GoTo ExitWithError_
    End If

    ' Clear the Clipboard
    x = EmptyClipboard()

    ' Copy the data to the Clipboard
    hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)
    ClipBoard_SetData = True
 
    If CloseClipboard() = 0 Then
        MsgBox "Clipboard could not be closed!", vbCritical, "API Clipboard Copy"
    End If
    Exit Function
ExitWithError_:
    On Error Resume Next
    If Err.Number > 0 Then MsgBox "Clipboard error: " & Err.Description, vbCritical, "API Clipboard Copy"
    ClipBoard_SetData = False

End Function