Copy multiple cells from excel column to clipboard

user_9649811 0
Viewed 2255

Example

Is there a way to copy multiple selected cells from excel as shown below? It always copies the whole range from the first selected cell to the last cell, rather than copying the values from selected cells.

A VBA code will be useful.

vba excel
1 Answers

The following will help, this will copy the specified ranges to the clipboard so you can paste them into Notepad:

Sub CopyToClipboard()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
'declare and set the worksheet you are working with, amend as required
ws.Range("B11:B12,B14,B18,B20,B22").Copy
'copy range to clipboard
End Sub

UPDATE:

A possible workaround for this would be to use a Temp worksheet and add the selected values into it and then copy that range into the Clipboard, a little long winded, but it would work:

Sub CopyToClipboard()
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
'declare and set the worksheet you are working with, amend as required
Dim work As Worksheet
Dim arr() As Variant
i = 0

For Each work In ThisWorkbook.Worksheets
    If work.Name = "Temp" Then
        Application.DisplayAlerts = False
        work.Delete
        Application.DisplayAlerts = True
    End If
Next
'if Temp worksheet exists then delete it

For Each c In Selection
    i = i + 1
    ReDim Preserve arr(1 To i)
    arr(i) = c.Value
Next
'above add the values from selection to an array

Set ws2 = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
ws2.Name = "Temp"
'add a temporary worksheet

For x = LBound(arr) To UBound(arr)
    ws2.Cells(x, 1).Value = arr(x)
Next x
'copy values from array into temp worksheet

LastRow = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
ws2.Range("A1:A" & LastRow).Copy
'copy continuous range from Temp worksheet
End Sub
user_86703720
Related