Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

 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

share|improve this answer
add comment

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
share|improve this answer
    
Thanks! Not really what I asked for, but it still might be a part of a solution. I want to copy the value in column 8 of the row I have selected (selected a cell). Then I am to use that value in further calculations... maybe: Cells(ActiveCell.Row, 8).Select Selection.Copy etc I'll try it! –  Tommy Jun 2 '10 at 21:03
add comment

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
share|improve this answer
    
datatoo's later suggestion is much cleaner (and more efficent; simply better, really) so I suggest using that! I'd vote datatoo's answer up if I could, but I lack the reputation at the moment. –  DMA57361 Jun 11 '10 at 10:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.