Cells not retaining formulas after macro runs

[ready2drum]

I have a macro that executes once the button is clicked. When clicked, the values in columns 'L' and 'N' rotate counterclockwise a list of first names, which display in pairs under column 'G', separated by a '/'. However, when the macro executes, several of the formulas under column 'G' break and it shows a 'REF!' error, and I'm not sure why.

For example, column 'L' contains 7 names starting in cell L5, and then L7, L9, L11, L13, L15, and L17. Column 'N' contains names starting in cell N5, and then N7, N9, N11, N13, and finally in N15. Column 'G' contains the end result which are the pairs of names (i.e. Kevin / Mark) starting in G5, and then G7, G9, G11, G13, and G15. Here's the basic formula for the values in column 'G'... example: Cell G5 contains the formula =+L5&" / "&N5....G7 =+L7&" / "&N7

See macro code below...

Please Login or Register  to view this content.

Not sure why the formula in some of the cells under column 'G' show a reference error, so if anyone can provide some assistance, I'd greatly appreciate it.

Thanks!

[davegugg]

When you cut a cell and paste it to a new spot, it replaces the cell that was in the new spot. The cell than ceases to exist, and any cell that was referencing it, ie in a formula, gets a #Ref! error. However, when you paste over a cell, it just makes the target cell the same as the copied cell. I don't know why Microsoft did it that way. So the errors are being cause by when you cut and paste in your code instead of copy and paste.

That said, you can streamline your code to make it more efficient like so:

Please Login or Register  to view this content.

[ready2drum]

I sincerely appreciate your guidance on this issue...I will update the macro and let you know how it all turns out.

Thanks!

[ready2drum]

It worked just fine! Thank you for your help!