"Paste Links"? That's a new one to me.



...Oh, I guess it isn't. I mean, I'd never noticed that option before, but I see it does what I was thinking about suggesting: copies 'SHEETNAME'!RC to each cell in sheet 2.

Two issues with that method that you, gib, may not think are issues:

1) The formatting doesn't go with it. This is easily gotten around; all you have to do is paste Formats, too, a one-time operation.

2) Empty cells in sheet 1 are represented on sheet 2 as 0s. Maybe you don't care; it depends on what you want to do with sheet 2. If you do care, but only about its appearance, you can easily tell sheet 2 not to display 0 values.

3) I said there were two issues but I just noticed a third: In my test of the Paste Links operation, I happened to use a worksheet that has hyperlinks in it. They don't transfer, either. Again, whether this matters to you depends on what you want to do with sheet 2.

In fact, I see now that using this method copies only the result of a formula, nothing else about the cells: conditional formatting, width and height, they're all left on sheet 1. If you want a real mirror...

Well, I can think of two ways to do that but I'm not sure either one will be worth the cost:

1) Do what Paul said, cut-and-paste the whole range from sheet 1 to sheet 2, but paste all, not just links. The problem with this is that it isn't automatic; you have to do it every time you want sheet 2 to reflect new changes in sheet 1. I suspect you considered this already; the point of your original post is that you want a way for it to happen without you having to take manual action.

2) Write a VBA macro for sheet 1 that runs automatically; every time anything changes in sheet 1, the changed range is automatically copied to sheet 2. I've never tried that, but I imagine it would work. But it would probably slow down your work. Maybe not, though; you could try it.