Greetings,
I have the following macro to delete unwanted columns, rename etc. to the attached Excel file:
Code:
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
I’d like to know if this macro can be enhanced and/or another macro can be created to further automate the following manual process.
After the macro ran, following are steps taken by end user to complete the monthly report process
Order Report
-
[*]Apply sort by column C: (Order Type) first and then by column A (Order Number)
[*]Delete STANDARD_RELEASE records from (Order Type) column
[*]Sort by column A: (Order Number)
[*]Sum ‘Distribution Amount’ on the basis of (Order Number)
Receiving report
-
[*]Delete blank records in the (Account Code) column
[*]Apply the same steps as the Order report, except do not omit STANDARD_RELEASE records in the (Order Type) column
Create a new worksheet
-
[*]Copy the Order Report
[*]Copy Receiving Report beneath it & highlighted it in red (to differentiate)
[*]Sort by column A: (Order Number)
[*]Visually check to see what order is received (where distribution amounts are same)
TIA,
Regards,
