How do I get an Excel macro to copy data from a specific column and the same row that is selected at activating macro?

Question

Example: I have selected any row and any column, let's say D6. The data I am looking for is in column H. So, I want the macro to copy the data in row 6, and column H, i.e. H6.

If I have selected G14, the macro shall copy the data in cell H14, etc.

Answer 1

 Range("H" & ActiveCell.Row).Copy
 ActiveCell.PasteSpecial

call it on Worksheet_SelectionChange

if you only want the value to use, just assign

Range("H" & ActiveCell.Row).Value 

to a variable and use it in your calculations and return where you will

Answer 2

I wrote a macro for that. It always copies the selected cell and pastes the data on column H at the same row.

Sub NewMacro()
    Selection.Copy
    Cells(ActiveCell.Row, 8).Select
    ActiveSheet.Paste
End Sub

Answer 3

You've had a few days so may have reached an answer already, but extending the answer already suggestedn, I think the below will do what you ask:

Sub CopyFromColH()
    'Save the current location
    Dim TargetCell As Range
    Set TargetCell = ActiveCell.Range("A1")

    'Copy from same row in H
    Cells(ActiveCell.Row, 8).Select
    Selection.Copy

    'Paste to original location, as saved above
    TargetCell.Select
    ActiveSheet.Paste
End Sub