Minimize File Size Of Macro-enable Workbook?

Sep 9, 2012

I have a macro-enabled workbook, in sheet1 i have two columns Country and Codes, they are in column C and D respectively. I used vlookup formula in generating the codes of the countries pasted in column C, they are being looked-up in sheet2 in column C and D as well. I already have 100000+ rows in sheet 2 under column C and D that's why the file is now heavy. Is it possible to encode column C and D in sheet 2 but still can be looked-up? or is there any other way to minimize the file size.

View 7 Replies


ADVERTISEMENT

Minimize Macro File Size

Dec 18, 2007

I have an excel file that is about 1,400 kB in size. This file is the backend for a complex model with many inputs, and I now want to build a front end for it using userforms and VBA. However, when i created the first userform, my excel filesize blew out to about 7,000 kB. When I added all of my userforms (about 16 of them because there are lots of inputs, organised into categories), the filesize grew to 77,000 kB.

I have tried everything to reduce file size, including clearing all unused cells, avoiding pivot tables of any sort, and have also exported then re-imported all of my VBA modules and userforms. But the situtation stays the same: no userforms or VBA and my filesize is about 1,400kB, add in all the userforms, and filesize is 77,000 kB. Is there a way to keep file size down with userforms, or is that just the way it is? Is the only way to keep file size down to have a single userform, all questions on that one form, perhaps with visible and invisible controls depending on the 'page' of questions being viewed?

View 7 Replies View Related

Minimize Size Of File

Jun 5, 2008

My file is currently about 70,000 mb,consisting of 10 spreadsheets.

Can i decrease the size by combining the spreadsheets?

Also, will nested formula take longer to load compare to 3 different cells referencing upon each other?

list that tells us how much each and different formula affects the size of the file?

View 4 Replies View Related

Workbook Containing Macro To Reduce File Size Locks Up When Shared

May 28, 2008

I've been having trouble recently with my workbook file size growing rapidly due to copy and paste commands and such. I found a very nice macro to reduce file size called ExcelDiet located at [url]
Option Explicit

Sub ExcelDiet()

Dim j As Long
Dim k As Long
Dim LastRow As Long
Dim LastCol As Long
Dim ColFormula As Range
Dim RowFormula As Range
Dim ColValue As Range
Dim RowValue As Range
Dim Shp As Shape
Dim ws As Worksheet

Application. ScreenUpdating = False
Application.DisplayAlerts = False

On Error Resume Next

This code works fine until I share the workbook. Can anyone take a look at this code and provide some information as to why this might be happening?

View 6 Replies View Related

Excel 2013 :: File Size Grows When Using Macro To Copy / Paste To New Workbook

May 9, 2014

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)?

View 2 Replies View Related

Workbook Growing In File Size

Aug 12, 2008

I have a workbook that I am working on that seems to be having file size growth issues.

All it is is 2 worksheets.

Worksheet 1- Columns A-S & 63 rows. That worksheet alone is 7mb.

no macros..No VBA..No formulas. Just straight text data with a handfull of auto-filters. I took the auto filters off and saved to see if that was the issue and it did not make a difference.

Worksheet 2- Columns A-S & 35 rows. This worksheet is almost 3mb.

Now I can make a change (when my computer catches up) to 2 cells and sometimes it will jump in size by 1mb or more.

View 9 Replies View Related

Automatically Enable Macro When Opening File

May 14, 2007

I'm working on a macro for work. It's a simple workbook with about 10 tabs. I would like for the user to open up the workbook but it not have the prompt foir enabling or disabling macros. Just on this one sheet, not other macros on other sheets.

View 9 Replies View Related

Macro Disable/enable Still Appears After Macro Deleted From Workbook

Oct 3, 2006

I have a question concerning Macros and the Disable/Enable prompt. My boss has a spreadsheet which has been used for years and he recently wanted to and did remove the macros from the spreadsheet (they were no longer necessary), but the disable/enable prompt still appears when the spreadsheet is opened. I replicated this in a test spreadsheet with a simple insert line macro and received the same results. Is there a way to remove the macros and the disable/enable prompt once they are removed? I know about setting the security to low to not see the prompt, but I would think that once the macros are deleted, the prompt should not appear any more.

View 2 Replies View Related

Skip Enable Macro On Open Workbook

Sep 19, 2007

I need my users to always enable macros when they open my workbook. Is there a way I can tell excel to enable macros and skip this initial question for users?

View 4 Replies View Related

How To Supress Enable Macro Option While Open A Excel File

Sep 7, 2008

How to supress Enable Macro option while open a excel file. I would like to do it using VBA in Macro of excel 2007 + versions.

View 9 Replies View Related

Workbook Enable Macro Protect / Unprotect The Sheet

Sep 4, 2009

I need a macro that I can have in any workbook enabling me to protect / unprotect the workbook that I have currently opened with a set password (let say "Password")

I would link this macro to a button in excel 2002. I have try the following but it doesnt work

Would it be possible that the button (first) works (second) understand whether or not protect / unprotected and do the opposite?

Sub Protect
Activeworkbook.protect password:="password", structure:=FALSE, Windows:=false
End Sub

View 9 Replies View Related

Run Macro In All Excel Macro Enable Workbook

Jan 27, 2014

I want to run a welcome macro in all excel workbooks. Whenever I open any macro enable workbook the welcome macro should run.

View 9 Replies View Related

Vba Code To Update All Pivot Table In Workbook But File Size Is100 Mb?

Mar 18, 2014

,i have file with 100 mb size.

a) i need to open the workbook and

b)Refresh all pivot tables in all sheet.

c)Also there are many formulas in the workbook that needs to be updated as well.

if i do it manualy i takes more time.......for opening and refreshing and i could see status bar running for excel calculation for updating all forumulas.

View 1 Replies View Related

File (with Macro) Keeps Growing In Size?

Jan 14, 2010

I have a workbook that grows in size on every use. I import a text file, manipulate the data, manually export it and delete the data from the workbook.
So why does it continue to grow in size? By manipulating I mean, add rows and text to the new rows. Attached is a copy of the workbook in native form (have not ran the macro) and a sample file to import, the excel file is 59kb in size and the test file is 9kb in size. I have seen my original file grow to over 500kb from repeating the same macro on the same file many times or running many different files for testing.

There is a CommandButton on Sheet1, it will open a userform. Click in the largest textbox and it will open a file dialog box in "C:". Place the supplied text file in "C:" or some other folder of your choice as you can navigate to any folder or drive from the dialog box. The macros that add lines and or text are "JumpToLabelName" (this runs first) and "InsertRows" which runs second. These two macros do all the work.

Take note:
I store information in the Registry, "GeoMeasure" under "VB and VBA Program Settings" (Normal windows key entry). You can search for "GeoMeasure" and delete it if you decide to test run the file.

View 5 Replies View Related

Open Then Minimize Workbook

Mar 18, 2009

I have tried searching for this code but just cant find (or remember) how i did it before.

I have a regular workbook (which changes name upon start up). In this workbook is a button that opens a linked database of products.

I want this button

1. to open the database C:/Database/db.xls
2. Minimize it
3. Maximize my regular workbook to its original state (this is where I am having the problem because the workbook name constantly changes)

View 23 Replies View Related

Minimize Workbook On Userform Launch

Oct 24, 2006

Is there a way to minimize Excel when a userform is launched, so just the userform is showing? I've tried using "Application.WindowState = xlMinimized" in the UserForm initialization procedure, but it minimizes both the workbook/application and the UserForm. I can click on the Excel window on the taskbar and just the UserForm will display--which is the goal--but I'm hoping there's a way to do it automatically without needing to click the taskbar icon after it's minimized.

View 2 Replies View Related

Reduce File Size Using Macro To Perform 500,000 Calculations

Dec 10, 2008

I have this formula that checks a line of values to see whether it is within the constraints of a separate list of values.

=if(AND(Data1!A4="count1",Data1!D4>="bin1",Data1!D4

View 9 Replies View Related

Macro To Save Workbook By Referencing Cell In Another Workbook For File Name And File Path

Mar 21, 2013

I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...

View 5 Replies View Related

Automatically Minimize The 2007 Ribbon On Opening A Particular Workbook

Feb 22, 2009

I would like to be able to automatically minimize the Excel 2007 ribbon on opening a particular workbook. My preference is to be able to apply that minimization to certain worksheets only, but if that's not possible then minimizing the ribbons on all sheets in the workbook will be ok.

Will I also need a BeforeClose code as well to reinstate the ribbon so that it is visible when opening subsequent workbooks?

View 5 Replies View Related

File Size... Vlookups Resulting In 90MB File

Apr 9, 2007

I have created a monster Excel file that contains loads of vlookups and is 95MB and extremely slow.

I guess that vlookup creates tables in the background, hence the file size. Does anyone have any ideas on how to decrease the file size while keep the vlookup functionality?

View 9 Replies View Related

Disable Red X But Keep Enable File - Close Working

Dec 24, 2008

I want to hide all toolbars and disable the red X upon opening of the spreadsheet, and to restore the main toolbars upon closing. That worked perfectly. (when I used the black code -see below)
I added in the red code as i wanted to disable the red X as well to ensure that users will always close the EXCEL worksheet via a Macro button.

The Macro will save the spreadsheet and close the worksheet and the Private sub should restore the toolbars.

View 7 Replies View Related

Enable Auto Refresh Of Web Queries When File Opened

Sep 22, 2006

I have a special use PC which will power a projector 24/7. When it reboots I want it to load Excel, open a spreadsheet, and then connect to the web to get data using web queries.

My problem is that I get a dialogue box each time the spreadsheet is opened: "Enable Automatic Refresh." I need to remotely reboot the PC from time-to-time so I can't click the button.

I have set my security levels to low and that does not help.

View 5 Replies View Related

Enable Outlining In A Shared/protected Workbook

Apr 22, 2009

I would like to know how the following code provided in the Excel Help Best Practice Forums , used to enable outlining on a protected sheet, can be applied to a shared workbook.

Private Sub Workbook_Open()
With Sheet1
.Protect Password:="Secret", UserInterfaceOnly:=True
.EnableOutlining = True
End With
End Sub

After enabling 'Share Workbook' and re-opening the file it presents the following error:

Run-time error '1004': Method 'Protect' of object '_Worksheet' failed

View 2 Replies View Related

Enable Command Button On Workbook Open

Oct 16, 2006

I am trying to enable a command button on opening the Workbook, However it dosent work. I have placed the command button on the worksheet. Programatically it gets disabled on clicking it once. So i want it to be reactivated on reopening the Workbook.

View 5 Replies View Related

Excel 2010 :: Enable Sorting A-Z In Locked Workbook

Jun 19, 2014

I have a workbook with a tab for each day of the month. This workbook is placed in a shared file and used by various yards in different locations. A new yard beginning the process is refusing to use it because due to formulas we must lock the workbook (contains many formulas and a live clock macro to track trip time) and they cannot sort A-Z. The sheets are labeled 1,2,3,4,5,6,7,8,9 and so on to 31. I know that you can write a Macro to allow sorting, but I cannot get it to work. the users CANNOT know the password due to hidden revenue etc. and formulas.

View 6 Replies View Related

Enable Macros Upon Opening A Workbook And Avoid Receiving The Message

Jul 14, 2009

What is the code to enable macros upon opening a workbook and avoid receiving the message "Security Warning-Macros have been disabled" in Excel 2007?

View 3 Replies View Related

Open File Macro: Same Directory As Macro Workbook

Nov 17, 2006

I created folder on my desktop to hold various documents. I have master excel document which contains forms and command buttons that open documents from same folder. Is there a way to change code instead of having full file path to specify current directory. I'm worried that if I move this folder to another location then my open file button will not work since location is changed.

Also when I open with command button word document, I have a button on word document to close. I can close document but word application is still active. Can I close word application with the document at the sam time. This is the code I'm using: "ActiveWindow.Close"

View 3 Replies View Related

File Size Too Big?

Sep 22, 2006

I have a workbook which has a file size much bigger than I would expect. To test it I inserted one blank worksheet and deleted everything else. I have removed a macro that was embedded in the workbook. I have removed all protection etc. I would now expect the file to be similar to a new workbook (circa 13k) but it is still 782kb even though there is absolutely nothing in it.

View 5 Replies View Related

Run Macro If File/Workbook Name Matches

Sep 5, 2006

I have a macro attached to a button. The file with this button is distributed among several people via outlook. The macro can only then work properly if the user saves the file on his/her hard drive with its original name. Outlook sometimes automatically saves the files as: Copy of... etc. I was wondering if there is a way to add a condition to the code so that the macro could only then be executed if the name of the saved file was exactly the same as stated in the code.

View 9 Replies View Related

How To Reduce File Size

Jun 11, 2014

I built a workbook that is used by our account team to summarize account status. Each worksheet has several cells that use conditional formatting to call attention to the status reported in that cell. Each manager sends their worksheets to a Senior manager who then consolidates into a single workbook containing approximately 50 worksheets. Once consolidated, the file size has grown to over 12 Mb and this needs to be reduced to a more manageable number, preferable 4 Mb or less. Prior to buliding the new version of this workbook, the file size was less than 2 Mb.

I have removed the Macros and believe the size is due to the conditional formatting in each worksheet.

Once the worksheets are compiled, is there a way to remove the conditional formatting and save the existing format - Similar to a "Paste Values" function? The senior manager does not need to change cell value to the formatting of the worksheet he receives could be saved.

To be specific - one cell turns has no color if the value is 10 or less, is yellow if 11 - 15, and Red if 16+. Once sent to the manager, the variance in this cell is not needed; he only needs to see the color associated with the value when sent. Next week he will receive an updated worksheet and a new value will be entered. The conditional formatting is only needed by the individual preparing the original worksheet.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved