Home/ Questions/Q 11902
Next
Answered
computing
  • 0
Asked: 2021-12-08T12:16:19+00:00 In:

Solved Automate Column Deletion & Rename Columns

  • 0

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

Share

    1 Answer

    1. 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.

      • 0