ABennett757
New Member
- Joined
- Mar 25, 2021
- Messages
- 10
- Office Version
- 365
- 2019
- Platform
- Windows
I am trying to implement code that copies a specific range to the clipboard so it can be pasted into a different Excel file. No problem there... However, I am asking the user if the items selected are to be deducted in this other Excel file and if they answer Yes then I want to quickly change the quantity of the item (column M) to be negative, copy the range and then restore the quantities back to the original. For example, if Item X has a qty of 1 and the user responded "Yes" indicated it is to be deleted, I want to temporarily change the qty to (-1), copy the range, and then change the qty back to the original value of 1.
This all works except that for some reason the copy action will not take place where it is located in the code. It will only execute the copy action at the very end and thus it does not copy the temporary quantity change as intended. I even added a 3 sec wait (which is commented out currently) to delay reverting column M back to original values and it still won't copy until that action completes... Any ideas? I'm stumped...
Dim Row1 As Integer
Dim Row2 As Integer
Dim Rows As Integer
Rows = Selection.Rows.Count
Row1 = Selection.Row
Row2 = Row1 + Rows - 1
Dim Answer As Integer
Dim i As Integer
Dim Deduct As String
Deduct = "No"
Answer = MsgBox(Rows & " Item(s) copied to clipboard to paste into CO Quote. Select YES if selected item(s) are being proposed for deletion.", vbYesNoCancel + vbDefaultButton2, "Copy to Change Order")
If Answer = vbCancel Then
Exit Sub
End If
If Answer = vbYes Then 'change quantities to deduct values prior to copy
For i = Row1 To Row2
Range("M" & i).Value = Range("M" & i).Value * (-1)
Next
Union(Range("I" & Row1 & ":I" & Row2), Range("M" & Row1 & ":Q" & Row2), Range("T" & Row1 & ":U" & Row2)).Copy
Deduct = "Yes"
Else
Union(Range("I" & Row1 & ":I" & Row2), Range("M" & Row1 & ":Q" & Row2), Range("T" & Row1 & ":T" & Row2)).Copy
Deduct = "No"
End If
' Application.Wait (Now + TimeValue("0:00:03"))
If Deduct = "Yes" Then 'revert back to original quantities
For i = Row1 To Row2
Range("M" & i).Value = Range("M" & i).Value * (-1)
Next
End If
This all works except that for some reason the copy action will not take place where it is located in the code. It will only execute the copy action at the very end and thus it does not copy the temporary quantity change as intended. I even added a 3 sec wait (which is commented out currently) to delay reverting column M back to original values and it still won't copy until that action completes... Any ideas? I'm stumped...
Dim Row1 As Integer
Dim Row2 As Integer
Dim Rows As Integer
Rows = Selection.Rows.Count
Row1 = Selection.Row
Row2 = Row1 + Rows - 1
Dim Answer As Integer
Dim i As Integer
Dim Deduct As String
Deduct = "No"
Answer = MsgBox(Rows & " Item(s) copied to clipboard to paste into CO Quote. Select YES if selected item(s) are being proposed for deletion.", vbYesNoCancel + vbDefaultButton2, "Copy to Change Order")
If Answer = vbCancel Then
Exit Sub
End If
If Answer = vbYes Then 'change quantities to deduct values prior to copy
For i = Row1 To Row2
Range("M" & i).Value = Range("M" & i).Value * (-1)
Next
Union(Range("I" & Row1 & ":I" & Row2), Range("M" & Row1 & ":Q" & Row2), Range("T" & Row1 & ":U" & Row2)).Copy
Deduct = "Yes"
Else
Union(Range("I" & Row1 & ":I" & Row2), Range("M" & Row1 & ":Q" & Row2), Range("T" & Row1 & ":T" & Row2)).Copy
Deduct = "No"
End If
' Application.Wait (Now + TimeValue("0:00:03"))
If Deduct = "Yes" Then 'revert back to original quantities
For i = Row1 To Row2
Range("M" & i).Value = Range("M" & i).Value * (-1)
Next
End If