+ Reply to Thread
Results 1 to 5 of 5

Trouble when pasting values into table (~20MG file)

  1. #1
    Registered User
    Join Date
    08-17-2015
    Location
    North Carolina
    MS-Off Ver
    2013
    Posts
    6

    Trouble when pasting values into table (~20MG file)

    Hello All,

    I have included a(n overly) detailed description of my problem below, but included a shortened summary of the issue at the bottom. I wish that I could attach an example of the problem, but I am working with sensitive data and it would be very difficult to dename. Thanks in advance!

    I have run into two problems in excel recently and both have to do with copy and pasting into a table. I have a decent sized model that I am working of of with ~10 tabs that pull off of a tab containing a data table with 25,000+ rows and 54 columns (20 of those columns contain formulas) - I would initially assume that the problem is tied to the size of the data, which is why I am sharing the dimensions.

    Every month I copy and paste new data to the table from a file with 34 columns and can range from anywhere 2,000-5,000 rows I generally copy and paste values of 2-5 columns at a time.

    Recently, when I try to paste (values, formulas, all, etc.) into the table, nothing shows up in the cells that I am pasting into - they are completely blank ('Problem #1). To troubleshoot this issue I have resorted to linking the cells in the table to the cells in data file for the new month and then filling down. It is a bit of a pain, but it has worked so far. Finally, I hardcode the linked cells by copying the columns in the table that are linked to the workbook and paste values so that the links are broken and the values remain. Unfortunately, when I do this, I have noticed that the top row data deletes/disappears. In other words, all of the data moves up a row and the first row of data vanishes completely (i.e. if I am copying and pasting values in cells C23000:C25000, C23000 disappears, C23001 becomes C23000 & C25000 moved up to cell C24999) (Problem #2).

    Executive Summary:
    Excel will not let me copy and paste from one data file into a large table in another workbook (no values are transferred, no error message)
    As a result I have to link the cells in the table to the cells in the data file (works fine)
    Once cells in the table are linked, I copy and paste values to remove the links and keep the values in the table
    When I paste, all of the pasted values move up a row and the first row of pasted data disappears completely (i.e. if I am copying and pasting values in cells C23000:C25000, C23000 disappears, C23001 becomes C23000 & C25000 moved up to cell C24999)

    Thank you in advanced for any help!!
    Last edited by Excel4157; 01-21-2016 at 01:33 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Trouble when pasting values into table (~20MG file)

    Before thinking about trying to sort this out, let's try to either prove or disprove whether it is the size of the file(s) causing this or some other gremlin has got into the works. From what you are saying, my first thoughts lean towards the former.

    One thing to try :
    Excel is not always good at letting bygones be bygones. It sometimes remembers that a sheet used to be a certain size and can't seem to forget it. This is particularly the case with files that have been amended many times.
    Try creating a new workbook, exactly like the current big one, and paste everything from the old file into it, and then save it. Compare file sizes - if the new file is a lot smaller, then that is the answer.

    Edit
    Make sure you only copy the ranges containing data - not complete sheets, complete rows, complete columns

    Also try pasting all the new monthly values into the new file to see if it works properly
    Last edited by Kevin#; 01-21-2016 at 02:32 PM. Reason: clarification

  3. #3
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Trouble when pasting values into table (~20MG file)

    If my suggestion in post#2 has no effect, then my next suggestion would be to delete half the rows in the big tab in a copy of the big file.
    Then save the file, and compare its size with the original.
    Then test by pasting in the new monthly values and see if the problem has gone away.

    Let us know
    Last edited by Kevin#; 01-21-2016 at 02:50 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Trouble when pasting values into table (~20MG file)

    Regardless of whether either of the above suggestions makes any difference, there are less memory intensive ways of moving values from one sheet to another.
    Who can blame the PC dropping the ball occasionally when you are cutting and pasting large amounts of data into a big workbook, with both workbooks open and other things running at the same time.

    What is the spec of your PC? especially its memory
    Which version of Excel are you running?

    Are you cutting and pasting manually or via VBA. If it's via VBA, post the code.
    Last edited by Kevin#; 01-21-2016 at 04:25 PM.

  5. #5
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Trouble when pasting values into table (~20MG file)

    Another quick fix until you get to the bottom of this would be to split your big file into 2
    - one containing only the very large sheet (A), and
    - the other one containing the ~10 other tabs (B)

    That would probably allow you to carry on with the current cutting and pasting routine into the big sheet
    The links pulling the information would be in workbook B and would not be increasing the size of A

+ 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. Pasting data all of a sudden ruins pivot table and file
    By i03theda in forum Excel General
    Replies: 0
    Last Post: 10-23-2013, 05:06 AM
  2. Replies: 2
    Last Post: 07-12-2013, 06:20 AM
  3. Loop Macro for coyping and pasting values into a table
    By burryumy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-24-2012, 07:39 AM
  4. I'm having trouble pasting data into specific cells in a table
    By phanjoe in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-21-2012, 11:36 PM
  5. Pasting excel table one after another in Ms-Word file Using VBA code
    By manish_ti in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-19-2012, 02:06 PM
  6. Trouble Pasting
    By Winchester in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-04-2011, 12:22 PM
  7. Replies: 1
    Last Post: 04-10-2010, 06:50 PM
  8. Pasting html table to Excel reformats certain values
    By peter.krantz@gmail.com in forum Excel General
    Replies: 1
    Last Post: 04-20-2005, 12:06 PM

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