+ Reply to Thread
Results 1 to 10 of 10

Create new workbooks accelerate the code with large data

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,880

    Create new workbooks accelerate the code with large data

    Hello everyone
    I have a userform from which I would select some parameters so as to finally create new workbooks based on the selected items from listbox
    The code works fine .. but as for the original data, it takes so much time ..
    Please Login or Register  to view this content.
    This code in the userform module (the userform name is "Settings")

    Select the sheet 9100
    Select the header CUSTNU
    and in the listbox select some items and finally click "Create Workbooks" button

    The code works fine for me ..
    What I am seeking for is to make it faster ...?
    Any ideas my friends
    Attached Files Attached Files
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

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

    Question

    Hi !

    Did you try an advanced filter ?

  3. #3
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,880

    Re: Create new workbooks accelerate the code with large data

    No I didn't try. But is Advanced filter will be faster ..?
    Can you please put that part for me as I like your approach most

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,489

    Re: Create new workbooks accelerate the code with large data

    As filtering and copying needs only a codeline using an advanced filter as you can see in the VBA help
    You just have to place the criteria range within a worksheet, column header first, criteria under

    Is it slow actually with your attachment ? If yes tell us with which criteria

  5. #5
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,880

    Re: Create new workbooks accelerate the code with large data

    In fact this is sample workbook so the code works fine here .. but in the original file which has about 200,000 rows, the code takes some time and sometimes excel hangs ...

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,489

    Arrow

    As creating, filling and saving workbooks needs time to execute
    To avoid Excel hangs add a DoEvents codeline before the Next codeline but it can slow down a bit more.

    So a tip is to use DoEvents statement each 100 iterations or more for example like If i mod 100 = 0 Then DoEvents

  7. #7
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,880

    Re: Create new workbooks accelerate the code with large data

    Thanks a lot my tutor for this useful tip

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,489

    Arrow

    Yasser,

    if you are in need, you already know the other forum where you can join me easily

  9. #9
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,880

    Re: Create new workbooks accelerate the code with large data

    I have posted the thread here too
    https://chandoo.org/forum/threads/cr...ge-data.42477/

  10. #10
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,880

    Re: Create new workbooks accelerate the code with large data

    Solved with the help of Mr. MarcL at the other link
    Regards

+ 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] macro code taking too much times to create various workbooks
    By geniusufo007 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-15-2018, 09:49 AM
  2. Copying data from a large number of workbooks
    By andrewc in forum Excel General
    Replies: 4
    Last Post: 08-12-2014, 12:26 PM
  3. Manage a large amount of workbooks with CSV imported data
    By Chris_SS in forum Access Tables & Databases
    Replies: 4
    Last Post: 05-22-2014, 07:27 PM
  4. Need VB Code to Create new workbooks out of a list on one workbook
    By mrgillus in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-21-2009, 02:29 PM
  5. Copy data from one large worksheet to multiple workbooks
    By richzip in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-25-2008, 02:05 PM
  6. [SOLVED] How do you move names and data between large Excel 2003 workbooks?
    By shore in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-22-2005, 08:05 AM
  7. How to create a worksheet to accelerate debts
    By Excel_Challenged in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-06-2005, 03: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