Search code examples
excelvba

I need to adjust QTYs in a table at the end of my Macro before resetting and closing/saving the workbook

What I thought would be the easiest part of this project is the hardest.

I have a request form in Excel and a Macro built in I created that sends a snippet of the requested items and attaches the workbook in an email before automatically saving and closing the workbook.

I want the email to attach the edited workbook with the added details. This works. What I want to happen next (after sending and before saving/closing) is to delete the name columns, location, etc. and reduce the "requested QTY" from the "Available Qty". Any help is much appreciated, I'm not really sure where to begin.

End of code and reference image below: (sending email works as does saving and closing the file)

Set objEmail = ObjOutlook.CreateItem(olMailItem)
    With objEmail
        .Importance = 2
        .To = "[email protected];[email protected]"
        .Subject = strSubject
        .htmlBody = strHTML
        .Attachments.Add wb.FullName
        .Send
    End With

    Range("Table1[QTY Requested]").Select
    Selection.ClearContents
    Range("Table1[[SOS Project Manager]:[Notes]]").Select
    Selection.ClearContents
    
    ThisWorkbook.Save
    ThisWorkbook.Close

End Sub

1

I tried the below to just clear the contents. This of course doesn't address adjusting the "available QTY" to it's new respective number.

    Range("Table1[QTY Requested]").Select
    Selection.ClearContents
    Range("Table1[[SOS Project Manager]:[Notes]]").Select
    Selection.ClearContents
Solution

  • Microsoft documentation:

    Range.PasteSpecial method (Excel)

    XlPasteSpecialOperation enumeration (Excel)

        Range("Table1[QTY Requested]").Copy
    Range("Table1[Available QTY]").PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlSubtract, SkipBlanks:=False, Transpose:=False
    Range("Table1[QTY Requested]").ClearContents
    Range("Table1[[SOS Project Manager]:[Notes]]").ClearContents