+ Reply to Thread
Results 1 to 3 of 3

File size grows when using Macro to copy/paste to new Workbook

  1. #1
    Registered User
    Join Date
    01-16-2014
    Location
    Portsmouth, England
    MS-Off Ver
    Excel 2003
    Posts
    6

    File size grows when using Macro to copy/paste to new Workbook

    This is going to be a challenge to explain, but will give it a try. I'm using Excel 2013 on Windows 7.

    I have an Excel file which has a macro that sucks in data from a number of separate files (two Excel, a varying number of csv). The macro grabs all the individual files and loads them into one of three worksheets with some formatting, sorting, and structuring on the way. All good so far.

    I then need to take two of the worksheets and copy them to a new Workbook which I can send out to some users. So I use the Workbooks.Add method, and use the Selection.Copy on just the data (UsedRange.Rows/Columns.Count to ensure it is only the data) and Selection.PasteSpecial with the xlPasteColumnWidths, xlPasteValues, and xlPasteFormats options to copy the data over. None of the data is filtered. Just straight data.

    I would expect the newly added workbook to be smaller than the one with the macros because the one with the macros has two extra sheets that I don't transfer, and the sheets I do transfer are identical.

    And that's where it gets weird. The file with the macros and more data is 18.7Mb, but the new one with only two sheets is 24.8Mb. One is .xlsm and the other is .xlsx (because it has no macros).

    I've tried opening the new workbook and saving as .xlsm (no material difference to the size) and as .xls (it got even bigger). I've opened the new workbook, gone to the end of the data and deleted all the blank rows and columns, but no effect (because the rows are already empty).

    Why would the file with less in it be so much bigger (or bigger at all)?

    Any ideas?
    Last edited by JabeSmith; 05-09-2014 at 02:59 AM. Reason: Added more info

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Re: File size grows when using Macro to copy/paste to new Workbook


    Hi,

    try the binary format (.xlsb) which works either with code or whithout …

  3. #3
    Registered User
    Join Date
    01-16-2014
    Location
    Portsmouth, England
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: File size grows when using Macro to copy/paste to new Workbook

    Oh my goodness. Down to 8.2Mb. Thanks......but......

    In non-tech speak, what is the binary format, does it have limitations, when should it be used and when shouldn't it be used, can it contain macros, etc., etc., etc..

+ 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. Copy and paste macro results in a large size file
    By rz6657 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-15-2014, 04:50 PM
  2. File size grows to unrealistic proportions
    By kevsvette in forum Excel General
    Replies: 1
    Last Post: 06-06-2007, 09:20 PM
  3. Shared Workbook inexplicably grows in size
    By Bob in forum Excel General
    Replies: 0
    Last Post: 08-18-2006, 08:35 AM
  4. Replies: 1
    Last Post: 10-17-2005, 04:05 AM
  5. Shared Workbook Grows Rapidly in Size
    By Burgerfield in forum Excel General
    Replies: 3
    Last Post: 04-12-2005, 05:06 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