Change contents of range, copy, then revert to original values not working as expected

ABennett757

New Member
Joined
Mar 25, 2021
Messages
10
Office Version
  1. 365
  2. 2019
Platform
  1. 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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
For reasons I don't understand, the copy operation is behaving as if instead of copying the cell contents to the clipboard, it is copying a link to the cell contents. The most expedient thing to do is probably to create a hidden worksheet that you can paste to instead of fully relying on the clipboard.

VBA Code:
    Dim Row1 As Integer
    Dim Row2 As Integer
    Dim Rows As Integer
    Dim Answer As Integer
    Dim i As Integer
    Dim Deduct As String

    Dim TmpWS As Worksheet                            'hidden worksheet that you create

    Set TmpWS = ThisWorkbook.Worksheets("MyHiddenWorksheet")    ' "MyHiddenWorksheet" is a hidden worksheet that you create
    If TmpWS.Visible = xlSheetVisible Then
        TmpWS.Visible = xlSheetVeryHidden
    End If

    'Clear any data & formats in TmpWS
    TmpWS.UsedRange.Clear

    Rows = Selection.Rows.Count
    Row1 = Selection.Row
    Row2 = Row1 + Rows - 1

    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 TmpWS.Range("A1")
        Deduct = "Yes"
    Else
        Union(Range("I" & Row1 & ":I" & Row2), Range("M" & Row1 & ":Q" & Row2), Range("T" & Row1 & ":T" & Row2)).Copy TmpWS.Range("A1")
        Deduct = "No"
    End If

    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

    TmpWS.UsedRange.Copy
 
Upvote 0
Solution
Great idea, thanks a million! Yeah, I'm still stumped as to why the copy action was behaving that way but thanks for the clever workaround. Works great. much appreciated!!!
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top