Improve VBA code to be more efficient, less long and use less memory

[WBravard]

I am new to VBA and not a programmer. I know what I did is NOT EFFICIENT. But for the most part it is working. To do this I copied macros and made adjustments.

Need to Copy 13 individual workbooks (each with 8000-15000 rows) into a single workbook . Then to each of the 13 sheets, added 4 columns, labels, formulas to the new columns all the cells used in other rows with values. Did some sorting and removing of duplicates on a few of the columns.

Got error that procedure was too large, so I cut it in about half. Then got insufficient memory - so I'm not getting memory released in doing this.

Can anyone help me make improvements to this?

I've attached two sample spreadsheets (there are 13 of them in total) I cut off the below code to not show all since it is just a copy/paste with change of sheet name.

Please Login or Register  to view this content.

[scottiex]

Maybe for the first part (combining the sheets) you can just do this

Please Login or Register  to view this content.

[scottiex]

I would either leave all coding of the format till the end or do it in a template in the sheet.
If you do it in the sheet you don't need to know the coding in vba to update it as needed and if you do it in the coding at the end it will be easy to find and edit without having to worry about how all your other actions effect the format.
Anyway in that spirit find the attached.

Have a look to see if you need add any further formatting at the end.

[mjr veverka]

Take a look, the main stem of your macro I kept.
But since I don't have a new excel, I have replaced some of the features.
Your code vba for 'RemoveDuplicates' and 'SortFields' was commented / blocked.
Check how it works in my version and how it is in your version - after the change of comment/blocking.