+ Reply to Thread
Results 1 to 6 of 6

Range Value Macro keeping "Data Signature"

  1. #1
    Registered User
    Join Date
    09-21-2012
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    13

    Range Value Macro keeping "Data Signature"

    All,
    I have a file that I run daily that has a large number of indirects as the back end - something like 3-400. I have a basic paste value macro that I run and send out the ranged valued version of the file. The problem is, the file, despite being range valued, is 3MB every time, despite only being a few worksheets. It's like there is a data signature that is holding on and keeping the file size large. Can anyone suggest a way to sort of let that signature go?
    Thanks

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Range Value Macro keeping "Data Signature"

    Do you mean that you change all of the formulas to values and the file size remains the same? I would not expect changing formulas to values to have any significant effect on the size of the workbook.

    I don't think the signature that you refer to exists but there are a few examples online on things to do to reduce file size. Might also be worth deleting all the macros and saving a copy to see what that does to the size of the workbook, I sometimes find my macros take up a suprising amount of space on some workbooks.

  3. #3
    Registered User
    Join Date
    09-21-2012
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Range Value Macro keeping "Data Signature"

    so, the way the file works is as follows:

    There is a daily units report that is sent out company wide, daily. due to terribly poor formatting and structure of that file (dates as texts, 24 different worksheets), i have to pull data into my file in order to run formulas from it.

    i pull data into a large grid from the daily file using indirects and use that as my raw data that a number of reports run from.

    from there, once the reports are updated with daily data, the macro copies only the simple worksheets (simple forumlas, not 3mb of data) and pastes values once they're in a new workbook. that new workbook, despite only being a few small worksheets, is 3mb.

  4. #4
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Range Value Macro keeping "Data Signature"

    Bit stumped without seeing the workbooks, I guess if there were loads of links to other workbooks that might make a difference, you could try running something like:
    Please Login or Register  to view this content.
    to get rid of all the links, maybe try and delete all the cells outside of the report and named ranges to.

  5. #5
    Registered User
    Join Date
    09-21-2012
    Location
    NJ
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Range Value Macro keeping "Data Signature"

    they're not exactly links, this is the forumla in each 120,540 cells:

    =+IFERROR(VLOOKUP($A5,INDIRECT("'[Company Sales and Returns with Retailers.xlsx]"&VLOOKUP(H$3,dwsadminCheck!$A$6:$B$29,2,FALSE)&"'!$A$9:$DG$3000"),HLOOKUP(H$4,dwsadminCheck!$A$31:$DE$34,4,FALSE),FALSE),"")

    regardless, the worksheet with these formulas is not brought into the new workbook.

  6. #6
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Range Value Macro keeping "Data Signature"

    I believe (at least in excel 2003) that when you reference another workbook in a formula excel creates a link to that workbook even if the formula is later deleted. In 2003 you can view the links excel has created by selecting edit->links. As far as I'm aware these links will be copied with the sheet. If you reference 120,000 different workbooks in formulas then conceivably excel could copy 120,000 links when you copy the sheet after deleting the formulas. This might cause your file size to inflate.

    This macro:
    Please Login or Register  to view this content.
    will delete all the links on a sheet, you could test if many links exist by doing something like:
    Please Login or Register  to view this content.

+ 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] Need Macro to compile data from "Weekly" sheets and sum up into "Monthly" summary sheet
    By mo4391 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-21-2013, 07:25 PM
  2. Replies: 3
    Last Post: 06-04-2011, 10:56 AM
  3. Macro Error with code "Range("A65536").End(xlUp).EntireRow.Insert"
    By lukasj13 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-30-2010, 08:48 PM
  4. Replies: 0
    Last Post: 07-17-2006, 09:45 AM
  5. Macro that runs entered value through "low" and "high" range
    By Vika.F in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-14-2005, 03:35 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