How to copy-paste absolute cell references temporarely as relative references in Excel?

Solution 1:

you could always try writing a macro for that. excel has a really nice macro recording tool that you could use too, and then just run it as needed (provided you make some changes first of course to the program)

dim firstLetter as String 
dim secondLetter as String 
dim firstNumber as integer 
dim secondNumber as integer 
dim firstReference as string 
dim secondReference as string 
dim contents as string 
firstLetter = inputbox("Where's the first column? (it's letter)") 
firstNumber = inputbox("And what's the first row? (just the number)") 
secondLetter = inputbox("What column is this going to be moved to? (the letter only.)") 
secondNumber = inputbox("And what row? (the number.)") 
contents = range(firstletter + cstr(firstnumber)).formula 
range(secondletter + cstr(secondnumber)).formula = contents 

Solution 2:

I found the workaroud for duplication.

Problem: Duplicate selection from starting cell B2 and place it on starting cell P2.

1. Create a new sheet.
2. From original sheet copy all what you want to duplicate and paste it into the new sheet at the same starting cell (if your selected rectangle start from B2, paste it into new sheet B2)
3. Move that selection in the new sheet on the right new starting cell (move it to starting cell P2).
4. Copy selection.
5. Paste selection on the original sheet on the right new starting cell (P2).