Excel VBA Copy Paste issue

Question

I am working with Excel VBA Copy Paste. Cell R7 has formula =Max ("C77:AD81").

R7 = Highest Value for Month
F7 = Highest Value to date
Q7 = the date F7 was achieved

What I am trying to achieve is if R7 > F7, copy R7 Value to F7 and change the Q7 to = today.

All I'm achieving is R7 changes to max of ("C77:AD81") and the remaining code doesn't work. My code below.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range, r As Range, rv As Long


    If Not Intersect(Target, Range("R7")) Is Nothing Then
        Set rng = Intersect(Target, Range("R7"))
        For Each r In rng
            'Change Best Peak Flow and Date Achieved
            Select Case r.Value
            Case Is > ("F7")
            Case Range("R7").Select
            Case Range("R7").Copy
            Case Range("F7").Select
            Case Range("F7").Paste
            Case ("R7") = ("F7")
            Case Range("Q5").Select
            Range("Q5") = Today()
            Application.CutCopyMode = False
            End Select
        Next r
    End If

End Sub

I think you have misunderstood the use of Select Case. I will take a look at your code in a moment. — K.Dᴀᴠɪs
– K.Dᴀᴠɪs, Commented Dec 29, 2017 at 0:05

AcsErno · Accepted Answer · 2017-12-29 08:12:38Z

1

My advice is not to use .select. You can program everything without a single .select. Recording and analyzing macros are very good starting point for learning VBA, but sometimes they are way too complicated. I prefer simple solutions so give this a try:

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("F7") <> Range("R7") Then
    Range("F7") = Range("R7")
    Range("Q5") = Date
End If
End Sub

answered Dec 29, 2017 at 8:12

AcsErno

1,6451 gold badge9 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

K.Dᴀᴠɪs · Accepted Answer · 2017-12-29 15:40:52Z

1

So, your rng object is only 1 cell, because you specified 1 target range of R7. With this being said, your For Each...Next statement is redundant.

I also wouldn't even use Select Case at all, but I will leave it in the event you later want to build off of it.

Give this a shot

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo ErrHandler    'Important to ensure events are reenabled
    Application.EnableEvents = False

    Dim rng As Range, r As Range, rv As Long
    Set rng = Intersect(Target, Range("R7"))
    If Not rng Is Nothing Then
        'Change Best Peak Flow and Date Achieved
        Select Case True
        Case r.Value > Range("F7").Value
            Range("F7") = Range("R7")
            Range("Q5") = Date
        End Select
    End If

    Application.EnableEvents = True
    Exit Sub
ErrHandler:
    Application.EnableEvents = True
    MsgBox Err.Number & vbNewLine & Err.Description

End Sub

edited Dec 29, 2017 at 15:40

answered Dec 29, 2017 at 0:17

K.Dᴀᴠɪs

10.1k13 gold badges36 silver badges46 bronze badges

8 Comments

Father Goose Over a year ago

The code still only updates ("R7"), ("F7") and ("Q5") don't update.

Ioannis Over a year ago

Good answer. Make sure you disable the events when you are inside the Worksheet_Change event and reenable them on exit. If you do not do so, changing a cell will trigger a recursive call to Worksheet_Change.

SJR Over a year ago

I think R7 needs to be copied to F7.

K.Dᴀᴠɪs Over a year ago

Thanks for the input Ioannis & SJR, you both are correct. @FatherGoose I have updated the code to reflect the issues that were presented.

Father Goose Over a year ago

The code update problem, ("R7"), ("F7") and ("Q5") don't update, still exist. Sorry people as a learner, I've obviously written some awful code.

|

Yunnosch · Accepted Answer · 2018-07-29 20:39:47Z

1

I solved it.
Here is the code I used.

Private Sub Worksheet_Change(ByVal Target As Range)

'Change Best Peak Flow and Date Achieved

If Range("R7").Value > Range("F7").Value Then
    Range("R7").Select
    Selection.Copy
    Range("F7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("Q5").Select
    Selection.Copy
    Range("K7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End If
End Sub

edited Jul 29, 2018 at 20:39

Yunnosch

26.8k9 gold badges46 silver badges66 bronze badges

answered Dec 29, 2017 at 23:47

Father Goose

871 silver badge7 bronze badges

Collectives™ on Stack Overflow

Excel VBA Copy Paste issue

3 Answers 3

Comments

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related