Non-relative copy

Topic

New Reply

I have a column of formulae. I want to make a copy of this column in the next column to the right but I want the formula to be exactly the same as the first column ie no relative reference changes. I know about relative and non-relative formula but I don’t want to change the formulae to be non-relative because it will take ages to do.

I have achieved it by copying the sheet, cutting and pasting the column out of the second sheet into the first and replacing all the sheet2 references with no sheet reference. There must be an easier way.

Reply | Quote

Replies

Hi Rob,
You can do what you want with some simple code:
Sub CopyRangeExactly()
With ActiveSheet
.Range(“C1:C30”).Formula = .Range(“B1:B30”).Formula
End With
End Sub
You just need to change B1:B30 to whatever your range to be copied is, and C1:C30 to the correct destination range. Still not sure why you want 2 identical columns though.
Hope that helps.

Reply | Quote

Rory,

That’s great. I will modify it so that it can be used generally and probably create an add-in for it.

Why do I want it? Don’t know but one of my constituents wants it for something. I do remember wanting to be able to do it once a long time ago but I just can’t remember exactly what it was for.

Thanks.

Off the topic: That’s a very flashy signature. Do the colours mean something ie a flag?

Reply | Quote

Yup – it’s the Irish flag (kindly provided by the Lounge’s GifMeister, DrkRealm)

Reply | Quote

Off topic. It’s VERY worth while asking Rory about all the other flags he’s entitled to. I don’t think even gifmeister could handle all of that!

Reply | Quote

Still off topic, but why in the litany of places in Rory’s bio, is Ireland nowhere mentioned, or are they the places he has never been to.

Reply | Quote

OK, now we’re off-topic, all replies on the “off-topic”, I’ve started a thread in Scuttlebutt- What is Rory’s background

Rory:

Showcase time!

Reply | Quote

For occasional use, you can replace the ‘=’ sign in your source range with ‘#@=’ (or a similar unique combination), copy the column and revert the replacemnt. Crude, but it works. Do not use the ‘ character, this is an one way street, you can transform a formula into a text string but not you will have to remove them one by one, let Microsoft explain this.

Reply | Quote

That’s an excellent little trick and for the amount of times we have to copy like that it will probably do. I shall pass it on.

Thanks

Reply | Quote

If it is just copying a column of formulae, there are two options that I can think of that may fit your needs.

First: Copy the first formula across, make the necessary changes, and then copy down.

Second: Copy the entire column across, and then use Find/Replace to re-align the column references.

Reply | Quote

David,

Unfortunately it won’t work if the column of formulae are not a copy down the column. Thanks anyway.

Reply | Quote

I’ve used both cri’s and David’s methods depending on the situation. However for cri’s I just insert a ^ at the front of the formula (trick I learned in Lotus) then find and replace in the column I copied to , in order to get rid of the ^. Often I have to switch back and forth between relative and absolute in a formula so that I can copy more formulas.

eg. ^=+C7/$B$7*L7 ^=+E7/$B$7*L7 ^=+G7/$B$7*L7

^=+C9/B9*L9 ^=+E9/B9*L9 ^=+G9/B9*L9

in the first row make B7 absolute then copy across
change B7, back to relative then copy down

it’s still not the easiest method but the more you can copy a formula, the less likely you are to make a mistake, and the quicker it is.

Inserting the ^ is also handy for when you want to copy the formula into a text cell for explanation.

Reply | Quote

A late extra:

If the formula could be copied downwards to create the column, then what I do is to go into edit mode on the top cell, select the whole formula (sometimes including the = sign and sometimes not), copy, and escape out of edit mode.
Go to the destination cell. Either type = or double click to get into edit mode. Paste. This copies the exact formula. Then copy downwards (can do with a double-click in the bottom right square of the cell sometimes) to create the second column.

Ruth

Reply | Quote

I always test these things out before I reply and I think that’s another handy little trick to stash away in the grey matter for later use. Thanks Ruth.

Reply | Quote