Copy Paste Loop & Offset

user_14039053 0
Viewed 25

I want to click a button and take cell E27 value and paste it into cell E35. Then, when I click it again, I want cell E27 value pasted into cell E36. Then, I want E27 value pasted into cell E37

Basically, E27 is constantly changing and I want to be able to take "Screenshots" of it whenever I want using a macro.

This is the code I have so far. It will paste E27 into E35, but when run again, it doesn't paste E27 into E36 etc.

Sub DataTrend()
Dim inRng As Range, outCell As Range, inCell As Range

Set inRng = Range("E27")
Set outCell = Range("E35")

Application.ScreenUpdating = False

For Each inCell In inRng
    outCell.Value = inCell.Value
    Set outCell = outCell.Offset(8, 0)
Next inCell

Application.ScreenUpdating = True

End Sub

excel
1 Answers

You would have to provide a method by which the macro can know which row to paste to. There are two systems to choose from.

  1. Determine a location where a counter can be placed: perhaps a cell (on a hidden worksheet) or a custom document property. Each time the button is pressed that counter is advanced by 1, and the row in that counter is used to paste the copy to.
  2. You might arrange your worksheet in such a way that the copy of E27 is always pasted to the next free cell at or below E37. This would do away with the need of a counter but would restrict the use of cells below row 37 in column E.

Your question doesn't specify which system you prefer. The code below chooses the second. It also doesn't require a button. It just records every change made to E27. Condition is that it's installed in the code module of the worksheet on which you want the action.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rt      As Long             ' Target row to write to
    
    With Target
        If .Address(0, 0) = "E27" Then
            Rt = Application.Max(36, Cells(Rows.Count, "E").End(xlUp).Row) + 1
            Cells(Rt, "E").Value = .Value
        End If
    End With
End Sub

If you prefer to operate the code with a button you can transplant the gist of the procedure to yours and install it in a standard code module, as you have already done.

user_76754370
Related