When you put a macro in the PERSONAL.XLSB file, it will apply to whatever the active workbook is. That may work fine for you, or it could be part of the problem you are having. In either case, this will not provide macros in a central workbook that can be used by all staff. PERSONAL.XLSB is just yours.
When you get a runtime error in VBA, the code window open and highlights the offending line of code in yellow. It will be very helpful if you tell us what line is showing up.
A subscript error can be caused by a couple of different things. In this case it is almost certainly caused by a reference to a worksheet that doesn't exist, most likely in one of these lines of code:
The macro as written does not pertain to any particular workbook, but pertains to whatever workbook is active at the time. So either your workbook does not have sheets with these names, or maybe the file that you want to use isn't really the active workbook.
The first thing I would do is to create a Module in the actual workbook that this code operates on and move this code in there.
Secondly, your code has macro recorder bloat. This code can be streamlined quite a bit. For example, the scrolling is only for display purposes and not really needed for what you're doing. Also, you don't have to Select a range to operate on it.
You are deleting columns B:H then H:M in two separate operations. That sounds like more than the formatting you described. Note that this is equivalent to deleting B:H and O:T in a single operation, since once you delete B:H, the data in column H is what started out in O. Hopefully that's really what you mean to do.
I would probably write this code like this, but don't have your file so testing was cursory:
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks