Create new workbooks accelerate the code with large data

**YasserKhalil** · 08-28-2019, 01:14 PM

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

**Marc L** · 08-28-2019, 01:29 PM

Hi !

Did you try an advanced filter ?

**YasserKhalil** · 08-28-2019, 01:45 PM

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

**Marc L** · 08-28-2019, 08:24 PM

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 …

**YasserKhalil** · 08-28-2019, 10:46 PM

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 ...

**Marc L** · 08-29-2019, 12:10 AM

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 …

**YasserKhalil** · 08-29-2019, 12:13 AM

Thanks a lot my tutor for this useful tip

**Marc L** · 08-30-2019, 12:05 PM

Yasser,

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

**YasserKhalil** · 08-30-2019, 05:15 PM

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

**YasserKhalil** · 08-31-2019, 12:51 AM

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

Create new workbooks accelerate the code with large data

LinkBack

Thread Tools

Rate This Thread

Display

Create new workbooks accelerate the code with large data

Re: Create new workbooks accelerate the code with large data

Re: Create new workbooks accelerate the code with large data

Re: Create new workbooks accelerate the code with large data

Re: Create new workbooks accelerate the code with large data

Re: Create new workbooks accelerate the code with large data

Re: Create new workbooks accelerate the code with large data

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] macro code taking too much times to create various workbooks

Copying data from a large number of workbooks

Manage a large amount of workbooks with CSV imported data

Need VB Code to Create new workbooks out of a list on one workbook

Copy data from one large worksheet to multiple workbooks

[SOLVED] How do you move names and data between large Excel 2003 workbooks?

[SOLVED] How to create a worksheet to accelerate debts

Bookmarks

Bookmarks

Posting Permissions