Advice on how to improve the attached macro (and tips)

attached please find a workbook with a macro.

the macro is used to copy and paste data , clear columns and create a data sheet.

it works and does what i need it to however id like to know if i can compact it or make it more efficent.

i am a VBA noob and i am sure that it can be improved. here is the code

Please Login or Register  to view this content.

if you can improve it please can you show me how and try to explain the alterations a little to allow me to understand better for future development (as this will be an ongoing project)

regards

steve

Heart of Crafts Master (Finished).xlsm

gentle bump

please any advice greatly received

ok thanks ill alter that.

any one else any pointers tips or pieces of advice?

Id get rid of all your loops to find the last row. And use what in Bold below

Please Login or Register  to view this content.

I would suggest altering the code names of the sheets in the vbeditor to something meaningful and then using those in your code where possible instead of relying on the tab names. also you can use faster methods to find the last used row in a column and turning off screenupdating will speed things up and avoid flicker

Please Login or Register  to view this content.

assuming the relevant adjusted code names

@ mike

thanks for that. only problem is that that will pick the entire column as there is a formula in there to fill it out
(like =IF(A1="","",B1)

which I suppose doesn't matter to much as its only pasting the values. but what would be faster?


@ Joseph

the anti flicker is great

how do you mean about editing names? do you mean to set them at the top so
wbPaper = sheets.("Paper")

thanks people.

no-I mean to edit them in the vb editor-see picture

ok i get you thanks for that.

through the tips here i have updated my code. it feels faster (the main thing is the flicker)

here is the new code. any more suggestions before i sign it off?

Please Login or Register  to view this content.

you have a couple unqualified range references

Please Login or Register  to view this content.

ought to be

Please Login or Register  to view this content.

note the 2 additional periods before Range and Cells

you could make other minor tweaks but nothing significant I can see beside the fact that using end(xlup).row is faster than looping down a column to find the last used cell

Originally Posted by JosephP

you could make other minor tweaks but nothing significant I can see beside the fact that using end(xlup).row is faster than looping down a column to find the last used cell

the problem with xlup.row it still pick the cells that are empty as they have been used (formulas / conditional formating etc) so it sets the print area to 500 rows long. is there a work around?

only not populating rows with formulas unnecessarily ;-)

i assumed that the code would remove the formulas prior to this when it does this

Please Login or Register  to view this content.

but when i tried the xlup it still went right down? can you think of any reason (conditional formatting etc) that might cause this?

if you copy and paste special a formula that returned "" the cell is not treated as empty. either keep the loop you have or don't populate the source sheet with formulas past the end of your data or only copy the data based on the usedrange from a different column-again using end(xlup)

ill leave the loop in as those columns are all dynamic and could grow or shrink (thats whay i have that in there)

thanks for all your help much appreciated.

i feel a little clearer about VBA now (this is my first real attempt at writing a decent macro)

regards

Stephen.