Dear Forum members,
I have an Excel worksheet that has formulae in cells e.g. Q5 and R5. I can copy and paste these cells to T5:U5 and then use the fill handle to fill T5:U91.
Or, I could copy Q5:R5 to T5:U91. The end result would be that Q5 would be copied to T5:T91 and R5 would be copied to U5:U91.

The worksheet is updated weekly so, the next week the formulae in T5:U5 will be copied to W5:X91. The following week the formulae in W5:X5 will be copied to Z5:AA91 etc.

I want to write a macro that will get the addresses of the source cells and paste them to the destination cells. I have done a tiny bit of VBA programming and think that I could use Range Object to achieve what I want. There may be a better way to achieve my aims but I am trying to understand how Range works. What I have done so far is to :

Calculate the address of the cell that has the first formula and Set Range Source1 to its address e.g. Q5
Calculate the address of the cell that has the second formula and Set Range Source2 to its address e.g. R5

set Range Dest1 to the address cell into which Q5 will be copied e.g. T5
set Range Dest2 to the address cell into which R5 will be copied e.g. U5

set Copy1 to Range("Source1:Source2") - this doesn't work!
set Paste1 to Range("Dest1:Dest2") - this doesn't work!

I haven't go as far as inserting 91 into the destination range or trying to initiate the Copy and Paste bit yet.

I may be barking up the wrong tree in trying to use the Range Object. I am hoping that someone in the Forum can help me out of my misery.
Regards,
Eire001