Offsetting from Active Cell, Relative Reference Range Copy- Can't Paste in Correct Destination

Tash Point O

New Member
Joined
Feb 12, 2018
Messages
47
Hello All,

I am trying to paste range A1:N1, which is a relative reference via an offset from the active cell (which can be A1, A7, A8, etc) to 3 rows above it and it's not working.

Now, I know it sounds like I'm trying to paste my copy to an address that doesn't exist but remember I've offset it and the A1:N1 range is a relative reference.

Here is my macro, all works well till I get to the destination.
Code:
Sub testing()

If Len(ActiveCell) = 6 Then
    ActiveCell.Offset(3, 12).Range("A1:N1").Copy ActiveCell.Offset(-3, 0).Range("A1")
    
End If




End Sub
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
If while your Active cell is A1 and you issue:
ActiveCell.Offset(3, 12)

You are Selecting Row 4

At the same time WITHOUT CHANGING THE ACTIVE CELL FROM A1 and you issue:

ActiveCell.Offset(-3, 0)


From A1 (with The Activecell being A1 using a -3
you cant go negative with A1 as the Base cell

Is the PROBLEM -- Dont you see that?
 
Last edited:
Upvote 0
This is why I kept saying in my opening post that I am using relative references. A1 is not really A1. A1 is a relative reference that can be A900, so in fact I CAN GO -3. That is not my issue. My issue pasting it in the right location row + column location, the - 3 is right. I need to go back up 3 but the column reverts back to A.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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