CODEWITHSUNDEEP
vbacopyrangeundo
Dina
Dina

Reputation: 11

VBA how to undo?

I need help with undoing the copy to worksheet2. My code works but I do not want both worksheets "Sheet2" and "Output2" to have the new data output. I only want "Output2" to have the new data output. How can I write an undo function or copy my "sheet2" data to a clipboard?

Undo and clipboard is the only option I can think of but I have not had success with implementation.

Any help is appreciated!

My data in "sheet2" is temperature and it varies from -21 degrees to -132.8 degrees. 

Sub ForEachTemp1()

For Each a In Worksheets("Sheet2").Range("A2:O21")

If a.Value <= 97 Then
    a.Value = True
ElseIf a.Value >= 97 Then
    a.Value = False

End If
Next a

Worksheets("Sheet2").Range("A2:O21").Copy Worksheets("Output2").Range("A2:O21")


For Each a In Worksheets("Sheet2").Range("A22:O42")

If a.Value >= 97 Then
    a.Value = False
ElseIf a.Value <= 97 Then
    a.Value = True

End If
Next a

Worksheets("Sheet2").Range("A22:O42").Copy Worksheets("Output2").Range("A22:O42")


For Each a In Worksheets("Sheet2").Range("A43:O63")

If a.Value <= -127 Then
    a.Value = False
ElseIf a.Value >= -127 Then
    a.Value = True

End If
Next a

Worksheets("Sheet2").Range("A43:O63").Copy Worksheets("Output2").Range("A43:O63")


End Sub

I want my current output (new data output) on worksheet "Output2" to be the same but I do not want the new data output to also populate on "sheet2".

Upvotes: 1

Views: 129

Answers (1)

Tim Williams
Tim Williams

Reputation: 166351

You can create arrays of True/False values using a worksheet formula and Evaluate:

Sub ForEachTemp1()

    Dim ws As Worksheet, wsOut As Worksheet

    Set ws = Worksheets("Sheet2")
    Set wsOut = Worksheets("Output2")

    PutArray wsOut.Range("A2"), ws.Evaluate("A2:O21<=97")
    PutArray wsOut.Range("A22"), ws.Evaluate("A22:O42<=97")
    PutArray wsOut.Range("A43"), ws.Evaluate("A43:O63>=-127")

End Sub

'utility sub to place an array onto a worksheet
Sub PutArray(rng As Range, arr)
    rng.Cells(1).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
End Sub

Excel is smart enough to know when the formula is an array formula, and creates a 2-D array of the result values, which you can assign directly to your output sheet.

Upvotes: 1

Related Questions