Copy to same cell address as another sheet (VBA)

C

Craig__

Board Regular
Joined
Feb 16, 2010
Messages
66
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Maybe something like

Code: 
Selection.Copy Destination:=Sheets("Temp").Range(Selection.Address)
 
Upvote 0
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.
 
Upvote 0
Try

Code: 
Selection.Copy Destination:=Sheets("Temp").Range(PrevCell.Address)
 
Upvote 0
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)
 
Upvote 0
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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Copy Data where Source Workbook contains Name that is in destination sheet
Replies
4
Views
150
howard
H
B
  • Locked
  • Question Question
copy range of cells
Replies
1
Views
88
Joe4
Joe4
N
  • Question Question
VBA - Intersect code being re-initiated
Replies
1
Views
211
Peter_SSs
Peter_SSs
M
  • Solved
Sum the values between two specified column headings - number of columns between will change
2
Replies
14
Views
281
Moley84
M
KlausW
  • Solved
VBA code to move picture from on sheets to another sheet
Replies
1
Views
57
KlausW
KlausW

Forum statistics

Threads
1,224,518
Messages
6,179,256
Members
452,901
Latest member
LisaGo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top