Copy multiple cells from excel column to clipboard
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.
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
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