I looked over your PERSONAL.xlsb and you have inherited quite a mess. Duplicate subs, Macros mixed in with Procedures, 70 Modules with less than half actually containing any code, an empty UserForm, no modules with significant Names. And as far as I can see, absolutely none of which should even be in a PERSONAL.xlsb workbook since all of it appears to only apply to the current Project under discussion in this thread.
PERSONAL.xlsb is named that because it is personal, you can't share it around the office, you can't use one that someone else emailed to you or attached to a post on VBAExpress. It can only be used on one computer, because all computers with Excel probably have their own PERSONAL.xlsb.
PERSONAL.xlsb is a unique Type of workbook and simply copying it merely creates a new Workbook of the same Type with a new name. I have attached a workbook of the non-PERSONAL Type with all the code from your PERSONAL.xlsb.
I "Removed" all empty Modules and Renamed some to indicate the type of procedure they contain, then moved some Procedures into those significantly named modules. I even did a little cleaning on a couple of Macros in preparation for turning them into proper Procedures. ( a Macro is recorded and has a tremendous amount of redundant and unnecessary steps in it. A procedure is well constructed and only takes the minimum steps to accomplish the task. Your code above is a Procedure and the code in Module20 is a Macro.) BTW, there is no sheet ("Ultimate") for the Macro in Module20 for the code to work on.
The first thing to do is open Excel from the start menu, which will open PERSONAL.xlsb .Delete all sheets except sheet1, then clear all contents and formatting from sheet1. Hide PERSONAL.xlsb.
In the VBA editor, right click all modules and the UserForm and "Remove" every one. then open the workbook I attached and in the VBA Editor, Left drag mod_Utilities into the PERSONAL.xlsb space. You will need to use the View Menu to insure that the Project Explorer is open, that is where you Remove and Drag modules.
Use the Tools >> Options Menu and in the "Editor" Tab of the DialogBox, Check all the CheckBoxes in the "Code Settings" Frame. On the "General" Tab, "Error Trapping" Frame, check the "Break on All Errors" CheckBox. Check both CheckBoxes in the "Compile" Frame.
Note the underscore in the name mod_Utilities. That places it at the top of the list of Modules. mod_Utilities is only for those Procedures that are used quite often in almost every Project and Workbook you create, such as GetLastRow, GetLastColumn, TrimStringsInCells, StripBlanks, etc. Instead of rewriting those little subs every time, just drag the Module into the new Workbook.
Remind me sometime to explain how I use my own Personal.xls. it is 276 KB in size but only 5 subs are even accessible from the Tools >> Macros menu in Excel and 4 of them are less than 8 lines long.
Back to the Attachment:
The author(s) of all that code did not indicate the purpose of any of the Subs and Macros. (see 1st comment line in Sub GetLastRow in Module mod_Utilities.) First you need to fix that problem, then organize all procedures according to their purpose and give the Modules significant names as I started for you. Delete or rename any subs with duplicate names, then delete all subs and Macros that are not used in your project. Remove any empty Modules. Add the line "Option Explicit" to the very top of all modules. See if you can convert all Macros into Procedures. Don't be afraid to ask how, it is a great VBA learning experience.
Reply With Quote