|OK, so we've added another layer of complexity to this: multiple users.|
In order to automate the deletion and renaming of columns headers, your users are going to have to use a macro. An Excel formula can't delete or rename a column, only a user or a macro can do that. A macro is a program written in VBA code that is basically a set of instructions that tell Excel what to do. In this case, the macro will simply do the things that the user would do manually.
This is where it gets a bit more complicated than just the code itself. In order for the users to use the macro, each user will need their own copy of the macro. They will also need to have macros enabled on their system. Since a macro is a program which could possibly contain harmful code, you can't use a macro to enable macros. If you could, then a bad person could write a macro, send it to a user, have the macro run itself and cause serious system issues. Therefore the first thing that would need to be done is that macros would need to be enabled on each user's system, either locally by the user/a helper or remotely if your IT environment allows for remote access of user's systems. See here for more info regarding enabling macros:
You will note that MS does not recommend that you enable macros. That is MS's way of relieving themselves of any responsibility should you end up with malicious code on your systems. If you read and understand the options available, and have other safeguards in place, you can make an informed decision as to whether or not you want to enable macros on your user's systems.
OK, once you made the decision to enable macros on your systems, you now need a way to have the macro available for your users when they need it. Macros can be stored in a number of ways. If they are stored within a specific workbook, they are only available within that workbook, or more precisely, only when that workbook is open. If your users are receiving workbooks from other sources, it would be a pain to have to copy the macro into each workbook before running it. The most common method to make a macro/macros available when the user opens the Excel application is to create a workbook called personal.xlsm and store the macros in that workbook. You would then typically hide the personal.xlsm workbook and save in the XLSTART folder. Any file that is stored in the XLSTART folder will open automatically whenever the Excel application is opened. I personally have dozens of macros stored in my personal.xlsm workbook so that they are always available to me regardless of which workbook I am working on at any given time. See here for more info on the personal.xlsm workbook:
Finally, you will want to have a easy way for the users to access the macro to delete and rename the columns. The best way to to that is to place an icon on the user's Quick Access Bar and assign the macro to it. That way the icon will be there whenever they open Excel and they can just click the icon to run the code.
So after all that, you will need the actual code to do the deletion and renaming. Before I can offer any code, I will need to know a few things about your workbook.
The basic questions are these:
1 - Are the columns to be deleted always the same. e.g A, F, J, Z, etc.? If so, I will need a list of those columns for both Worksheets. If it is not always the same columns, then I will need to know how to identify which columns to delete.
2 - Are the column names that are to be changed always the same or will they change each time a workbook is received? Again, I would need to know exactly what names need to be changed and what they need to be changed to.
Think of a macro as nothing more than the user who would be making this changes manually. If you were write down a set of instructions that you would hand to new user telling them to do this, then this, then this, etc. you would need to be very specific, right?
Well, you need to be that specific in explaining the tasks that you want the macro to preform so that I can translate those instructions into the code that will perform those steps.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.