Copy data from cell above in specific cell below-CodePudding

I have a table with years above and i want to copy it to specific cells below:

I want in example to copy years in (H1; I1) to (H78; I78) by choosing H78 and use Ctrl V as shortcut , etc... i dont want to fill all blank cells, just specific cells. Thanks you and sorry for my bad english.

CodePudding user response：

I think you're playing with fire here but this should work for you ...

Private rngSelectedRange As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Application.CutCopyMode = xlCopy Then
        Application.EnableEvents = False
        Me.Cells(Target.Cells(1, 1).Row, rngSelectedRange.Cells(1, 1).Column).PasteSpecial xlPasteValues
        Application.EnableEvents = True
    Else
        Set rngSelectedRange = Target
    End If
End Sub

... paste that code in the worksheet you want to apply this logic to like shown below.

To make it work, just copy one of the headers and then start clicking around.

To stop it from working, hit ESC and it will disable the Copy operation.

It could be made more advanced by allowing multiple rows at once but that hasn't been built in.