Copy to same cell address as another sheet (VBA)

[Craig__]

I can’t get my Copy Destination range below to work. I would be so grateful if an expert could help.

On a sheet called "Temp" is a block of selected cells. On the same sheet, I want the block copied to the same column and row as PrevCell (i.e. the same cell address as the "Expenses" sheet).

Sub Copy_to_same_cell_address

Dim PrevSheet As Worksheet
Dim PrevCell As Range

Set PrevSheet = ActiveSheet ' "Expenses" sheet
Set PrevCell = Range("D" & (ActiveCell.Row))

Sheets("Temp").Activate

Selection.Copy Destination:=Range("D" & (Sheets(PrevSheet).Range(PrevCell).Row))

End Sub

 

[VoG]

Maybe something like

Selection.Copy Destination:=Sheets("Temp").Range(Selection.Address)

 

[Craig__]

Hi Vog, yes something like that, but I want the block to be copied to the same cell address as PrevCell on the other sheet. I've tried:

Selection.Copy Destination:=Range(PrevCell).Address

but that doesn't seem to work either.

 

[VoG]

Try

Selection.Copy Destination:=Sheets("Temp").Range(PrevCell.Address)

 

[jbeaucaire]

Maybe:

Selection.Copy Destination:=Sheets("Temp").Range("D" & Selection.Row)

 

[Craig__]

Thanks for your help Vog and jbeaucaire. Much appreciated.
I have now come up with this, and it seems to work ok:

Selection.Copy Destination:=Range("D" & Range(PrevCell.Address).Row)

 

[Craig__]

I'm trying to develop this a bit further so it also references the column as well as the row. I've tried this but can't quite get it right:

Selection.Copy Destination:=Range(PrevCell.Address).Column) & Range(PrevCell.Address).Row)

 

[VoG]

My reply here http://www.mrexcel.com/forum/showpost.php?p=2867463&postcount=2 should do exactly that without having to set any variables.

Alternatively

Selection.Copy Destination:=Cells(prevcell.Row, prevcell.Column)

 

[Craig__]

Vog, your alternative code is the perfect solution. Thank you so much for your time on this.