Copy multiple cells from excel column to clipboard

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.

1 answer

  • answered 2018-11-08 09:06 Xabier

    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