I have a Macro that takes a long time to run, how to make it faster

[dmcgov]

i have a macro that takes info from an estimating sheet and populates another sheet called master. everything does work ok with the routine but it takes 2-3 seconds to run per fixture on each sheet (i have any where from 1 estimating sheet to 130 sheets) and if i have a full complement of estimating sheets then the routine takes 260-390 seconds, which doesnt seem like a lot but when im demo'ing the software to my supervisor, then 4-6 minutes is a long time. so i have been toying around with the idea of creating an array of the items listed on each sheet (there can be 180 items per each estimating sheet).

so the first question is, is creating an array going to save me time on each sheet? if not, then there is no need to redo the routine, i will use it as is.

so the second question is, if i can use an array to get my data copied to the master sheet, what would that routine look like? not asking for you to do any work yet, just wanting to know how the format of the routine would be.

thanks for everyone's help so far, you guys are the bomb!

[joe31623]

Post the macro. If you're storing information from cells into variables, I don't think you'll save any time, but there are often ways to optimize code. I've found that avoiding .Select, .Copy, and .Paste methods make the biggest impact in my code, but here is a more comprehensive list of optimization techniques.

http://www.cpearson.com/excel/optimize.htm

[dmcgov]

yah i know that i have quite a few select statements, plus copy and paste too.

here is the macro, it is quite large.

Please Login or Register  to view this content.

the jist of the code is that i am copying from sheet "A" to my estimating1 sheet. the range that I copy is from B57:I352 and i want to filter that range so that only rows that have a value in row B that are greater than 999. i want to only copy columns B,C,G,H,I to the master sheet. if you need more info, please let me know. ive been told that i have complicated steps, but they all to what i just said above. so filter B57:i352, include rows that cell "B" has a value greater than 999 and only copy 5 columns to the master sheet. if you need to know where they go in the master sheet, just ask.

[bulina2k]

Ok, let's forget about that code.. That code is not... the best example. Actually it could be a "don't do like this" example.
Back to the goal:
1. You want to copy the 8 columns (B57:I352) from sheet A to sheet 'estimating1'. First question is where in the estimating1 sheet ?
2. You want to filter the data in the estimating1 sheet by B > 999 criteria and then copy the 5 specified columns of the filtered data from estimated1 to Master sheet. Where in the Master sheet?

[dmcgov]

Create 1 - With Random Values for upload.xlsm

i am uploading the spreadsheet now. these have both the master and estimating1 sheets in it. is that what you are asking for?

[zbor]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[dmcgov]

does that conform to the rules, im not sure how to edit it further. please give me advice if i need to change it again.

[dmcgov]

is the post still locked? i can't tell.

[bulina2k]

So, what do you need? what is your goal for this file?

[dmcgov]

so the reason that im asking for help is that the routine takes about 2-3 seconds per fixture (the A,B,C sheets are estimating sheets) to sum up on the BigMaster sheet. i am toying around with how to create an array and only include certain columns per row to go to the Master sheet. Most of the code that i use is from the macro recorder doing the brunt of the heavy lifting. the other code is where the recorder failed to do what i wanted so i (with help) coded those sections.

what the macro does is copy a range (B57:I469) from each estimating sheet (A,B,C...ZZZZZ) to the estimating1 sheet, from the estimating1 sheet extract the appropriate rows to the master sheet. through liberal use of formulas i get it down to only rows that have a sum in column I (provided that the cell in column C is greater than 999) and then copy that to bigmaster (which contains all the line items for all fixtures). this then gets imported into our construction software.

so what i want extracted using this range B57:I352 is any row where the cell in column C is greater than 999, then copy the cells in columns C,D,G,H,I to the master sheet. i can take it from there.

[bulina2k]

This should get you started:

Please Login or Register  to view this content.

[dmcgov]

so i copied the code below (one small change) and nothing happened. having said that, this is exactly what i wanted for the routine, filter a range and copy the data. heres the code that i used:

Please Login or Register  to view this content.

[dmcgov]

so part of the issue is that from my estimating sheets, (where it starts with B column) when i copy it to my estimating1 sheet, it goes into the C column, so i changed this part of your code. Range("$C$57:$I$352").AutoFilter Field:=2, Criteria1:=">999". my question is the Field:=2, what does that signify?

in this next line of code, lr = est1.Cells(est1.Rows.Count, "B").End(xlUp).Row, does the "B", signify the B column (and needs to change to C??).

and as for my master page, i have a header row on Row 1. so it needs to start at row 2.

i have added my spreadsheet if you need to look at it.
Create 1 - With Random Values for upload.xlsm

[bulina2k]

Nothing's happened probably because estimating1 is empty. You said that the macro copies [should copy] a range (B57:I469) from each estimating sheet (A,B,C...ZZZZZ) to the estimating1 sheet, but WHERE in estimating1 sheet? On what range? As you can see in my #4 post i already asked this..

[dmcgov]

if you review my upload, the estimating1 sheet is there. i prepopulated it from my estimating sheet. since i have a header row on my master sheet, the data should go to row 2. as for the range on my master sheet, i use C,D and H,I,J columns only. the same for the estimating1 sheet.

[bulina2k]

your uploaded file 's estimating1 sheet is empty if you review your upload...

[dmcgov]

i just downloaded the upload and estimating1 sheet is there and populated.

so can you explain your macro so i can make it work?

thanks

[bulina2k]

This is the third time I download the file and other than some selected range the estimating 1 sheet is empty. Check the picture below.


estimating1.jpg

[dmcgov]

Estimating1 for upload.xlsm

i just exported estimating1 to a new spreadsheet. let me know if there is data there.

[dmcgov]

so i got it! and thanks for your help. see code below, it works like a champ

Please Login or Register  to view this content.

and its faster by about 50%!