+ Reply to Thread
Results 1 to 16 of 16

Copy and paste bug? Date is pasted to the filtered (hidden) cells too. What's wrong?

  1. #1
    Registered User
    Join Date
    07-16-2014
    Location
    UK
    MS-Off Ver
    2016
    Posts
    90

    Question Copy and paste bug? Date is pasted to the filtered (hidden) cells too. What's wrong?

    I use tables. I use filters to display some row data only.
    Excel appears to have inconsistent behaviour.
    Sometimes if I copy and paste data, it only applies to the displayed cells. Sometimes it applies to both displayed and hidden cells.

    Let's say I have a filtered table A1 to A6. The status of each cell:
    A1 Header row, displayed
    A2 displayed
    A3 displayed
    A4 hidden
    A5 displayed
    A6 displayed
    I copied cell A2 by Ctrl+C. Highlighted A3 to A6 (bear in mind A4 is hidden). Pasted by Ctrl+V.
    It is expected to paste only to A3, A5 and A6.

    But in some cases (of course not in the above example), it will paste the data to the hidden cells too. I fail to spot the pattern.

    Does anyone know how this will happen?
    Is there any surefire way to copy and paste so it will NEVER paste to the hidden cells?
    Thank you.
    Last edited by mastertonn; 10-21-2018 at 01:57 AM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Copy and paste bug? Date is pasted to the hidden cells too. What's wrong?

    As far as I know, excel will always paste to hidden columns.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,416

    Re: Copy and paste bug? Date is pasted to the hidden cells too. What's wrong?

    If you type this into Google:

    paste to visible cells only Excel
    you will get a list of useful tutorials about how to overcome this.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Registered User
    Join Date
    07-16-2014
    Location
    UK
    MS-Off Ver
    2016
    Posts
    90

    Re: Copy and paste bug? Date is pasted to the hidden cells too. What's wrong?

    Quote Originally Posted by FDibbins View Post
    As far as I know, excel will always paste to hidden columns.
    No, it isn't.
    Follow the steps in the above example. It works to paste only to the visible cells.
    Unfortunately it sometimes does not work in some unknown situations. It makes this copy-paste operation particularly dangerous and you have to avoid it if you don't know exactly how Excel determines when to paste to the visible cells only, when not.

    BTW I'm using Excel 2016.

  5. #5
    Registered User
    Join Date
    07-16-2014
    Location
    UK
    MS-Off Ver
    2016
    Posts
    90

    Re: Copy and paste bug? Date is pasted to the hidden cells too. What's wrong?

    Quote Originally Posted by AliGW View Post
    If you type this into Google:



    you will get a list of useful tutorials about how to overcome this.
    Thank you. I took a look at some of them.
    Some require the use of a macro. I prefer not to use macro since it will erase the undo history list.
    Some are pretty clumsy. I have to copy and paste like this a lot, which means a lot of time is wasted.
    It appears to be no quick and easy solution, or perhaps I'm missing something. Please tell me if you do know one. Thank you.

  6. #6
    Registered User
    Join Date
    07-16-2014
    Location
    UK
    MS-Off Ver
    2016
    Posts
    90

    Re: Copy and paste bug? Date is pasted to the hidden cells too. What's wrong?

    Inconsistent behavior is spotted.
    If you copy one cell and paste to multiple visible cells by Ctrl+C and Ctrl+V, it works to paste to visible cells only.
    If you copy one cell and paste to multiple visible cells using any(?) paste special feature, it paste to both visible and hidden cells.
    I don't understand why Microsoft coded it this way. It is annoying.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,416

    Re: Copy and paste bug? Date is pasted to the hidden cells too. What's wrong?

    Inevitably there are some things about the programming of the software that we like and some that we don't, some that we get, and some that we don't get. It is very unlikely you'll get an answer to your question here, although I imagine your question is rhetorical. If you find it annoying, then go to the Microsoft Office User Voice site and add your comments there: this way, if there are enough complaints about it, Microsoft may do something about it.

  8. #8
    Registered User
    Join Date
    07-16-2014
    Location
    UK
    MS-Off Ver
    2016
    Posts
    90

    Re: Copy and paste bug? Date is pasted to the hidden cells too. What's wrong?

    Well I don't think there is any technical barrier or limitation, but I couldn't care much about that.

    Let's see if we could find more inconsistency. I could copy-paste at ease without risking overwriting data if we know clearly how Microsoft behaves in different cases.

    I see there are tons of complaints about it BTW. Microsoft is well aware of it but it does not seem to care.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,416

    Re: Copy and paste bug? Date is pasted to the hidden cells too. What's wrong?

    I do not disagree with you!

    By the way, please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Copy and paste bug? Date is pasted to the hidden cells too. What's wrong?

    Quote Originally Posted by mastertonn View Post
    If you copy one cell and paste to multiple visible cells by Ctrl+C and Ctrl+V, it works to paste to visible cells only.(
    Sorry to disagree, but I just tested - again - and it copied to the hidden column using both CTRL C/CTRL V as well as dragging
    H
    I
    J
    K
    L
    1
    Entered copied hidden copied copied
    2
    aaa aaa aaa aaa aaa
    3
    aaa aaa aaa aaa aaa


    (and I am using 2016, too)

  11. #11
    Registered User
    Join Date
    07-16-2014
    Location
    UK
    MS-Off Ver
    2016
    Posts
    90

    Re: Copy and paste bug? Date is pasted to the hidden cells too. What's wrong?

    I believe you simply hide the column using the drop-down menu. It will paste to the hidden column in this case.
    Try exactly the steps in the example of the first post. It will paste to the visible cells only.
    I use filters to hide rows.
    Last edited by AliGW; 10-21-2018 at 01:22 AM. Reason: Unnecessary quotation removed.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,416

    Re: Copy and paste bug? Date is pasted to the hidden cells too. What's wrong?

    Mastertonn - please read post #9 again. You were asked not to quote whole posts, especially not the post immediately before your own. Please do not ignore moderation requests. Thank you.

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Copy and paste bug? Date is pasted to the hidden cells too. What's wrong?

    Quote Originally Posted by mastertonn View Post
    Let's say I have a filtered table A1 to A6. The status of each cell:
    A1 Header row, displayed
    A2 displayed
    A3 displayed
    A4 hidden
    A5 displayed
    A6 displayed
    I copied cell A2 by Ctrl+C. Highlighted A3 to A6 (bear in mind A4 is hidden). Pasted by Ctrl+V.
    It is expected to paste only to A3, A5 and A6.
    That is exactly what I did.

    But I have just noticed 1 thing - you are talking about hidden columns AND filtered rows.

    C/V WILL copy across hidden columns but will NOT copy over filtered rows.

    SO which are you referring to, columns or rows?

  14. #14
    Registered User
    Join Date
    07-16-2014
    Location
    UK
    MS-Off Ver
    2016
    Posts
    90

    Re: Copy and paste bug? Date is pasted to the hidden cells too. What's wrong?

    Oh so sorry AliGW, I forgot it. I don't understand why but the quick reply does not seem to work properly. I clicked on it but it did nothing. It took some seconds to load to a new page if I clicked on it again.

    Anyway, never mind, I could just scroll to the reply box to type the reply. Thank you.
    Last edited by mastertonn; 10-21-2018 at 01:55 AM.

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,416

    Re: Copy and paste bug? Date is pasted to the hidden cells too. What's wrong?

    Yes, that's what I do.

  16. #16
    Registered User
    Join Date
    07-16-2014
    Location
    UK
    MS-Off Ver
    2016
    Posts
    90

    Re: Copy and paste bug? Date is pasted to the hidden cells too. What's wrong?

    Hi FDibbins, what I mean is using filters to hide data row.

    To be more specific, put those data in A1 to A6.

    A1 Header
    A2 some text
    A3 some text
    A4 another text (hidden cell)
    A5 some text
    A6 some text

    Then set a filter in A1. Select to hide A4.
    Copy A2 by Ctrl+C.
    Highlight A3 to A6.
    Paste by Ctrl+V.


    C/V WILL copy across hidden columns but will NOT copy over filtered rows.
    It should not copy over filtered rows, but there are some cases it will.
    Here's the one I find which is reproducible.
    If you copy one cell and paste to multiple visible cells using any(?) paste special feature, it paste to both visible and filtered (hidden) cells.
    There should be some more. I have yet to spot the exact steps/patterns.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] It sets date format wrong when copy and paste
    By zanshin777 in forum Excel General
    Replies: 18
    Last Post: 01-16-2016, 08:16 AM
  2. copy paste macros
    By rrk2008 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-31-2014, 06:42 AM
  3. Automatically copy date from DTpicker and paste into hidden worksheet
    By msexceljones in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-12-2012, 07:08 PM
  4. Copy and not paste into hidden cells
    By grekis in forum Excel General
    Replies: 0
    Last Post: 10-31-2012, 09:16 PM
  5. Replies: 0
    Last Post: 02-05-2012, 03:50 PM
  6. Copy-paste from csv to xls macro > wrong date format
    By Gero in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-30-2009, 06:12 AM
  7. [SOLVED] Pasted date in Excel 2003 is wrong e.g. 05-16-2006 is copied,05-15-2002 is pasted
    By Jaap van der Veen in forum Excel General
    Replies: 3
    Last Post: 08-11-2006, 09:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1