Paste to Same Row as Highlighted Cell

[helpexcel]

Hi - How do I use the highlighted cell as the row to paste into? So if some highlights Cell A10, then items would be pasted into row 10. thanks!

 

[mumps]

If by "highlights" you mean selects A10 by clicking on it, then you can use "Activecell.Row" to refer to the row of whatever cell is selected.

 

[helpexcel]

that's what i mean, and works perfectly. thanks!

 

[mumps]

You are very welcome.

 

[helpexcel]

So i seem to be having a problem, in that when the new workbook opens the selected cell changes. Is there a way to stop that? Or is there a way I can find the text in A1 of the new sheet in column A of the existing sheet and use that as the row number?

 

[mumps]

Yes there is but I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[helpexcel]

This is the code i'm working with. I'm trying the find row method instead of active cell.

Dim ws1 As Worksheet
Set ws1 = Sheet1


Set Ws2 = WB.Sheets("Import")
Set CsNm = Ws2.Range("I27")


With Sheet1
'Dim found As Range
Set found = .Range("B5:B25").Find(what:=CsNm)
End With


With Ws2
.Range("C10").Copy
ws1.Range("D" & found).PasteSpecial xlPasteValues
.Range("C20").Copy
ws1.Range("E" & found).PasteSpecial xlPasteValues
.Range("C30").Copy
ws1.Range("F" & found).PasteSpecial xlPasteValues

 

[helpexcel]

Aha! I used .Range("D" & found.row) works perfectly!

 

[mumps]

Glad it worked out.