Solved Automate column deletion & rename columns

Microsoft Office 2010 professional (full...
May 8, 2014 at 07:13:38
Specs: Windows 7
Hi
I have a workbook with two reports (Order & Receiving) that include about 20 columns each.

1. I'd like to delete unwanted column and focus only the one needed to reconcile order & receiving reports.

2. I would like to rename columns that are labeled differently in receiving report to match those in order report (below) for example order number in column A of the order report is the same as order number (column L) in receiving report. In this case columns are labeled the same. But, I would like Receipt Date (A) to be Order Date (same as column E in order report)


Order Report Receiving Report
Order Number (A] Order Number (L)
Order Date (E) Receipt Date (A)
Order Type (F) Order Type (N)
Blanket Order (H)
Supplier (L) Store Name (Q}
Total (W) Sub Total (X)
Account Code (X) Account Code (Y)

What would be the best tool (Access or Excel) to automate this process?

TIA,
Regards


See More: Automate column deletion & rename columns

Report •


✔ Best Answer
May 21, 2014 at 12:52:46
As I was working on the code to accomplish your task, I came up against a requirement that was not 100% clear.

When you said that the unwanted columns "should be deleted", I took that to mean that the entire columns should be deleted, not just the data in the columns. The following code is based on that assumption. If that assumption is not correct, just let me know.

I suggest that you test this code in a backup copy of your workbook since macros cannot be undone. I added 2 checks to the code so that the code isn't run accidently:

1 - The code checks to make sure that both the "Order Report" and "Receiving Report" sheets exist in the workbook. If they don't, the user is informed that this might be the wrong workbook.

2 - The code checks to see if "Store Name" has been changed to "Supplier". If it has, the user is informed that workbook has already been modified.

Sub Delete_Rename_Columns()
'Make Sure We Are In The Correct Workbook
  On Error GoTo noSheetName:
   Set ws = Sheets("Order Report")
   Set ws = Sheets("Receiving Report")
  On Error GoTo 0
'Make Sure The Code Hasn't Already Been Run
   If Sheets("Receiving Report").Range("D1") = "Supplier" Then
     MsgBox "It Appears That This Workbook Has Already Been Modified"
      Exit Sub
   End If
' *** Order Report ***
  With Sheets("Order Report")
'Delete Any Unwanted Columns In A:Z Range
    .Range("B1:D1,G1,I1:K1,M1:W1,Y1").EntireColumn.Delete
'Delete All Columns Beyond Column G
    .Range(.Cells(1, "H"), .Cells(1, .Columns.Count)).EntireColumn.Delete
  End With
' *** Receiving Report ***
  With Sheets("Receiving Report")
'Delete Any Unwanted Columns In A:AA Range
    .Range("B1:K1,M1,O1:P1,R1:W1,Y1:Z1").EntireColumn.Delete
'Delete Any Columns Beyong Column G
    .Range(.Cells(1, "G"), .Cells(1, .Columns.Count)).EntireColumn.Delete
'Rename Columns
    .Range("A1") = "Order Date"
    .Range("D1") = "Supplier"
  End With
 Exit Sub
noSheetName:
    MsgBox "This Does Not Appear To Be The Correct Workbook." & _
            vbCrLf & vbCrLf & _
           "                     Required Reports Not Found."
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
May 8, 2014 at 07:40:09
This could be done with an Excel macro, but I have a couple of requests first.

1 - Please explain why you need to automate this process. Based on what you've posted, it seems like a fairly simple task to do manually - unless you have a large number of workbooks to do this in.

2 - Please elaborate on the "deletion" request. Do you have a list of columns to delete by name, or by Column letter, etc? Any macro would need to be told which columns to delete, or perhaps which columns not to delete, depending on which list was longer.

3 - Please click on the following line and read the instructions on how to post example data in this forum, then repost the table from your original post. That will make it easier for us to read, as well as teach you the proper method to post example data so that the columns line up. While in this particular case it's fairly apparent what you are trying to show, learning the correct method may come in handy in the future. Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
May 8, 2014 at 19:13:09
Thanks for clarifying the forum posting instructions. To answer your questions:

1. Yes, my workbook has two reports (order & receiving) and includes 20+ columns and over 700 records. The order report includes (columns A – AB) & the receiving report (columns A-AA). But, I’m interested in only few columns.

2. In regard to deletion, it doesn’t matter if columns are deleted either by name or by column letter. What I’d like to do is to rename selected columns in the receiving report to match that of order report, and once this is done delete all unwanted columns from both reports.

Regards,


Report •

#3
May 8, 2014 at 19:50:57
You haven't explained why you need to "automate" the deletion and renaming of the columns. If this is a one time task it seems like you could have completed the task in the time it took you to post.

What am I missing?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
May 9, 2014 at 07:35:08
This reason for the automation is that end users get this file form outside vendors etc. and they manually go through deleting unwanted columns, renaming etc. So to answer your question it’s not a onetime task.

Regards,

message edited by ocm1


Report •

#5
May 9, 2014 at 18:17:21
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:

http://office.microsoft.com/en-us/h...

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:

http://excelhints.com/2010/11/29/ho...

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.


Report •

#6
May 18, 2014 at 15:15:17
Thanks DerbyDad for your detailed description for the possible solutions and sorry for late reply.

below are answers to your questions:
1. Are the columns to be deleted always the same?
Yes, the only columns needed from the order report are below and no renaming of columns needed for the order report:
Order Number (A:1)
Order Date (E:5)
Order Type (F:6)
Blanket Order (H:8)
Supplier (L:12)
Total (W:23)
Account Code (X:24)
Distribution Amount (Z:26)

For the receiving report some columns should be renamed to match the order report as follows and the rest should be deleted:

Order Number (L:12) -->no need to rename
Receipt Date (A:1) --> rename to Order Date
Order Type (N:14) -->no need to rename
Store Name (Q:17) --> rename to Supplier
Sub Total (X:24 ) --> rename to Total
Account Code (Y:25) -->no need to rename
Distribution Amount (AA:27) -->no need to rename

The two reports (order & receiving) are in the same workbook. Every month the end user gets the data from another source and the user then runs order and receiving report for the specified dates to capture data. Once the formatting (delete/rename) is done the goal is to load these into one report for reconciliation purposes.

BTW, enabling macro will be fine.

TIA,
Regards,


Report •

#7
May 19, 2014 at 05:49:55
I now need the exact details related to each sheet so that I can put together some code for you.

1 - What is the exact name of the sheets ("the two reports (order & receiving)") where the changes have to made?

2 - Are the names of the columns (Order Number, etc.) in Row 1 of each column? If not, what Row are the names in?

3 - I see a column for Blanket Order (H:8) in the "order report" but not in the "receiving report". Is that correct?

Finally, I have one last comment to make regarding the automation of this process.

It appears that you will be deploying and using this macro as a regular part of running your business. Please be aware that any code offered in a free help forum such as this comes with no warranty, but more importantly, comes with no support. If things change in your IT environment or if there is a change in the reports that your users receive from the outside source, it's very possible that the code will no longer work as expected. At that point you'll be back to your manual process.

It would behoove you to either learn how write VBA code and/or find someone in your organization that is willing to learn so that this business process has some internal support.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
May 20, 2014 at 19:20:58
Thank you, first, one quick note that it was determined the Total (W:23) in Order Report and Sub Total (X:24)in the Receiving Report are not needed as Distribution Amount can be better used instead.

Answer to your questions are below:
1 - What is the exact name of the sheets ("the two reports (order & receiving)") where the changes have to made? The exact name of the sheets (Order Report and Receiving Report)

2 - Are the names of the columns (Order Number, etc.) in Row 1 of each column? If not, what Row are the names in? Yes, A1, B1 etc.

3 - I see a column for Blanket Order (H:8) in the "order report" but not in the "receiving report". Is that correct? Yes, that is correct

And finally, your comment is well understood and very well taken.

Regards,


Report •

#9
May 21, 2014 at 12:52:46
✔ Best Answer
As I was working on the code to accomplish your task, I came up against a requirement that was not 100% clear.

When you said that the unwanted columns "should be deleted", I took that to mean that the entire columns should be deleted, not just the data in the columns. The following code is based on that assumption. If that assumption is not correct, just let me know.

I suggest that you test this code in a backup copy of your workbook since macros cannot be undone. I added 2 checks to the code so that the code isn't run accidently:

1 - The code checks to make sure that both the "Order Report" and "Receiving Report" sheets exist in the workbook. If they don't, the user is informed that this might be the wrong workbook.

2 - The code checks to see if "Store Name" has been changed to "Supplier". If it has, the user is informed that workbook has already been modified.

Sub Delete_Rename_Columns()
'Make Sure We Are In The Correct Workbook
  On Error GoTo noSheetName:
   Set ws = Sheets("Order Report")
   Set ws = Sheets("Receiving Report")
  On Error GoTo 0
'Make Sure The Code Hasn't Already Been Run
   If Sheets("Receiving Report").Range("D1") = "Supplier" Then
     MsgBox "It Appears That This Workbook Has Already Been Modified"
      Exit Sub
   End If
' *** Order Report ***
  With Sheets("Order Report")
'Delete Any Unwanted Columns In A:Z Range
    .Range("B1:D1,G1,I1:K1,M1:W1,Y1").EntireColumn.Delete
'Delete All Columns Beyond Column G
    .Range(.Cells(1, "H"), .Cells(1, .Columns.Count)).EntireColumn.Delete
  End With
' *** Receiving Report ***
  With Sheets("Receiving Report")
'Delete Any Unwanted Columns In A:AA Range
    .Range("B1:K1,M1,O1:P1,R1:W1,Y1:Z1").EntireColumn.Delete
'Delete Any Columns Beyong Column G
    .Range(.Cells(1, "G"), .Cells(1, .Columns.Count)).EntireColumn.Delete
'Rename Columns
    .Range("A1") = "Order Date"
    .Range("D1") = "Supplier"
  End With
 Exit Sub
noSheetName:
    MsgBox "This Does Not Appear To Be The Correct Workbook." & _
            vbCrLf & vbCrLf & _
           "                     Required Reports Not Found."
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
May 23, 2014 at 11:45:37

DerbyDad03,

Thank you very much it worked beautifully.

I noticed column orders not the same in both reports. Is it possible to modify the code so that in receiving report Order number is in A1 and Order Date is in B1 etc. (just like in order report)?

The next step is to combine these two reports (order report and receiving report) into once master sheet to reconcile. To do so, I plan to delete dups first, sort the report and use the built in Excel consolidate. Please let me know if you have a better idea.

Regards,


Report •


Ask Question