Page 1 of 2 12 LastLast
Results 1 to 15 of 23

Thread: enhance macro

  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    enhance macro

    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,
    Attached Files Attached Files
    Last edited by RetiredGeek; 2015-01-26 at 15:48. Reason: Added Code Tags

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi OCM

    If you save an excel file as ".xlsx", then all vba is 'removed'.
    Can you post your file as a ".xlsm" file, so that the vba is retained?

    Now, if I 'manually' add your code to the .xlsx sample file you attached (and then rename as a .xlsm file), you macro will give the 'not correct workbook' message because your sheetnames are different.

    How is your macro executed? Do you use a button? Are there other workbooks open etc??

    I'm sure we could help you, but we need a little more info.

    zeddy

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Zeddy,

    If you save an excel file as ".xlsx", then all vba is 'removed'.
    Can you post your file as a ".xlsm" file, so that the vba is retained?
    --> As per your suggestion I saved the attached file as macro enable workbook.

    Now, if I 'manually' add your code to the .xlsx sample file you attached (and then rename as a .xlsm file), you macro will give the 'not correct workbook' message because your sheet names are different.
    --> I modified the sheet names the same way as in the macro. But, you brought up a good point. The
    sheet names will change every month (e.g. Order report 0115, Order report 0215 etc.) what is the best
    way to make this work?

    How is your macro executed? Do you use a button? Are there other workbooks open etc??
    -->Currently I run the macro manually, but eventually my goal is to create a button
    I'm sure we could help you, but we need a little more info.


    Regards,
    Attached Files Attached Files

  4. #4
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    This code (insert a REGULAR module and put it there) should do what you want for sheet names and culling columns/rows. However, you don't say where you want these subtotals, sheet 2 doesn't have order numbers,
    Assumes sheets sorted as shown in example

    Sub FixWorksheetsSAS()
    'Make Sure We Are In The Correct Workbook
    If Left(Sheet1.Name, 5) <> "Order" Then Exit Sub
    'check if already done
    If Sheet2.Range("D1") = "Supplier" Then
    MsgBox "It Appears That This Workbook Has Already Been Modified"
    Exit Sub
    End If
    ' *** Order Report ***
    With Sheet1
    'Delete Any Unwanted Columns In A:Z Range
    .Range("B11,G1,I1:K1,M1:W1,Y1").EntireColumn.Delete
    'Delete All Columns Beyond Column G
    .Range(.Cells(1, "H"), .Cells(1, .Columns.Count)).EntireColumn.Delete

    'added to delete rows
    With .UsedRange
    .AutoFilter Field:=3, Criteria1:="STANDARD_RELEASE"
    .Offset(1).SpecialCells(xlCellTypeVisible).EntireR ow.Delete
    .AutoFilter
    End With

    End With

    ' *** Receiving Report ***
    With Sheet2 '("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"
    'added
    With .UsedRange
    .AutoFilter Field:=3, Criteria1:=""
    .Offset(1).SpecialCells(xlCellTypeVisible).EntireR ow.Delete
    .AutoFilter
    End With
    End With
    End Sub
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Don

    re: "sheet 2 doesn't have order numbers"

    ..it's in column [L] (before processing), and column [B] (after processing)

    zeddy

  6. #6
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    OK. I see that now but hesitate to go any further if the OP is NOT coming back. We really need to see an AFTER example on sheet 3
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  7. #7
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you both.
    Dan, I'll try your code, but to answer your questions,

    1. However, you don't say where you want these subtotals ---> in a separate sheet would be great.
    2. sheet 2 doesn't have order numbers --->order number in the receiving report (2nd report) is on column L. I think you are aware of this...
    3. Assumes sheets sorted as shown in example  No, my original sheets are not sorted.

    Regards,

  8. #8
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    As requested, post a before/after example showing both sheets before and one after sheet in the same fiie.
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  9. #9
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Don,

    Attached is the sample, let me know if this is what you were looking for.
    I tried your code and got ‘Object required’ error

    TIA,

    Regards
    Attached Files Attached Files

  10. #10
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    I think the attached does as desired
    Attached Files Attached Files
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  11. #11
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Don,

    Thank you, I’ll try this tomorrow in my actual file, but it works in the sample workbook as planned.

    The only thing I noticed is blank records in the account code column for order numbers (51258, 51274, 51303, 51304).

    I think this is because we need to delete blank ‘account code’ values in the receiving report.

    This is on my original post where I listed the manual process steps for receiving report.

    Once we fix this and hit the Click Here button, I think everything in accounting code column should be populated.

    TIA

    Regards,

  12. #12
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    OK. Do you want it as in YOUR example or in MINE without the blanks.
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  13. #13
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Don,

    In both, if possible. If I have to choose one, I like your example w/o blanks.

    TIA,

    Regards

  14. #14
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    OK. See attached.
    BTW, You don't say how the sheets are populated. I would think that could also be automated in the ONE click.
    Attached Files Attached Files
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  15. #15
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Don,
    Thank you, it is working now. I'm not sure if the following answers your question..
    re. how the sheets are populated and to further automate the rest in one click.

    So far, everything worked as intended the next step in the manual process was to perform a visual comparisons between the two reports (order & Receiving). Let's just take for example order # 51300 and it's corresponding distribution amount. Following are possible scenarios:
    a. If order # 51300 exists in order report, but not in receiving - we can display a message like "not in receiving",
    or 'missing" etc.
    b. if order # 5103's distribution amount = $465.67 in both reports we get "0" (order - receiving) or 465.67 - 465.67
    and we are not interested in this value
    c. if distribution amount for order report is different than the receiving distribution amount (delta) we want to know this
    value
    So, once everything works as intended, what is the best way to utilize this macro every month? For example, I'll be getting January's file in the beginning of next week?

    TIA
    Regards



    As an example let's take

    Following is o Omit records that = 0
    o Missing  record is available in order report, but missing from receiving report
    o Negative values 
    o Positive values 

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •