Hello, so I am surprised this basic thing doesn’t work - I have a table and and autofilter. I select column of data, ctrl+c, paste it to notepad - fine I see only the rows which I have copied, BUT
when I paste it to the spreadsheet program from Microsoft it pastes all rows like the filter was not applied! Why is this happening and why isn’t there in clipboard only what I actually have selected?
Try to paste the content of the clipboard into a ODF type file in the LibreOffice. (It works for me in my LO 6.1.6) Then select and copy it again from the second file.
I have not M$ Excel, i can not try it directly with any LO version.
Which LO version are you using?
I jut tried it with a pure text file: I can copy-paste only a filtered cell range from a LO spreadsheet into a .txt file.
Works for me. Sitting in front of a Windows PC with LO 7.3. I filter a list, select the filtered range and paste to Notepad.
OQer also reported that it worked with notepad.
If I understood correctly, he complained that it didn’t work with Excel.
BTW: A clipboard may contain data in many “flavors”: I personally don’t know if there can be “filtered” and “unfiltered” at the.same time. The reported issue seems to tell it can.
May there be a flavor marker “deliver filtered” not regarded by Excel? Who knows all about clipboards?
Was there a “clipboard manager” installed/running on the original system,?
2 Likes
Indeed, the clipboard flavours of Excel and Calc are not fully compatible to each other. You have to open all spreadsheets in one application either Excel or Calc.
1 Like
If you paste from Calc to Excel you will not retain formulas either. Although pasting from Excel to Calc formulas are retained. Looks like a limitation of Excel
Tested with LO 7.3.2.2 & Excel 2019
1 Like
It might be that from the various formats offered in the clipboard Excel picks the DIF or RTF format as the possibly richest format it understands, which for whatever reason (seems to be a bug to me) both include filtered-out rows, contrary to all other formats. Can be reproduced in Calc by manually selecting DIF or RTF from the Paste button’s arrow-down list.
2 Likes
I can’t paste formulas between different versions of Calc, e.g. from 7.2.6.2 to 7.3.2.2.
I see that Excel includes 3 different Excel formats on the clipboard.
No problem here. i.e. formulas from 7.2.6.2 paste fine in 7.3.2.2, Linux, Gnome.
Hooray for clipboards.
I apologise for deviating from original question. Formula not pasting in Windows, bug tdf#148590
In my case, I’m using an ODS file, I think you are using a CSV file.
If this is the case, just convert your file into an ODS one.
From now on, you can copy the filtered content, I tried it on Excel live (Microsoft Excel - Collaborez sur des feuilles de calcul Excel), and it’s working as intended.
In contrary, when I paste on Gedit it doesn’t work that well because it pastes the hidden column and rows but take in count the filtering.
In last resort if it works from LibreOffice Calc to Notepad then you can just copy from Notepad to Excel
1 Like
You can manipulate the clipboard from your Excel xlsm. The Excel macro below grabs the clipboard as text then creates an array of array based on splitting the text by tab and crlf, then assigning to cells.
Assign the macro to your favorite keystroke in Excel. It could go into your macro library, etc., of course. Notice that to paste you just click in the top left corner of your target range then run the macro.
Option Explicit
Option Base 0
Sub GetClipBoardText()
Rem Clipboard use from Siddharth Rout/StackOverflow
Dim DataObj As MSForms.DataObject
Rem To use MSForms, you must reference Tools>References>Browse>System32\FM20.DLL
Rem which will then be listed as Microsoft Forms 2.0 Object Library
Dim Data() As String
Dim Records() As Variant
Dim Contents As String
Dim TargetRange As Range
Dim Index As Integer
Dim Row As Integer
Dim Column As Integer
Const SheetBase = 1
Rem Excel uses 1-based referencing
Rem Get data from the clipboard.
Set DataObj = New MSForms.DataObject
DataObj.GetFromClipboard
Rem Set target range
Set TargetRange = Application.Selection
Rem Just top left cell
Rem Get clipboard contents
Contents = DataObj.GetText(1)
Rem Only 1 is implemented, means text
Rem Set into individual records/fields
Data = Split(Contents, vbCrLf)
ReDim Records(UBound(Data) - LBound(Data) + 1)
For Index = LBound(Data) To UBound(Data)
Records(Index) = Split(Data(Index), vbTab)
Next Index
Rem Copy into cells
For Row = LBound(Data) To UBound(Data)
For Column = LBound(Records(Row)) To UBound(Records(Row))
TargetRange.Cells(Row + SheetBase, Column + SheetBase).Value = Records(Row)(Column)
Next Column
Next Row
End Sub
The screenshot below shows normal pasting on the left and use of this macro on the right, when the clipboard source is an auto-filtered set of rows from LO Calc:
