MrExcel Publishing
Your One Stop for Excel Tips & Solutions

mirroring cell contents on another sheet


Posted by Todd on January 24, 2002 11:07 AM

I am trying to find a way to exactly mirror the contents of a cell on one sheet to another. I not only want to duplicate the text but it's formatting as well. This needs to happen automatially without the user having to manually copy and paste the text formatting of the source cell.

In other words I would like to enter a formula into cell (sheet2!A56) that would cause it to always contain the text and formatting of (sheet1!A1) including any bolded or italicized text.


Posted by Mark O'Brien on January 24, 2002 11:30 AM

This is an absolutely terrible attempt. Perhaps someone can expand upon it. It does what you want it to do, so long as you change the cell contents and not just the formatting. Anyway, give it a try. Put this code on the worksheet that is being changed, i.e. not the duplicate page.

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Copy
Sheets("Sheet2").Range(Target.Address).PasteSpecial xlAll
Application.CutCopyMode = False
End Sub

Posted by Juan Pablo G. on January 24, 2002 11:46 AM

Mark, you could do that in one line

Target.Copy Sheets("Sheet2").Range(Target.Address)

Juan Pablo G.

Posted by Todd on January 24, 2002 11:52 AM

As I understand it this mirrors the whole sheet not just a specific cell. Is there a way to modify this to mirror a single cell or even a specific range of cells?

P.S. Thanks for starting me down the right trail! Mark, you could do that in one line Target.Copy Sheets("Sheet2").Range(Target.Address) Juan Pablo G. : This is an absolutely terrible attempt. Perhaps someone can expand upon it. It does what you want it to do, so long as you change the cell contents and not just the formatting. Anyway, give it a try. Put this code on the worksheet that is being changed, i.e. not the duplicate page. : Private Sub Worksheet_Change(ByVal Target As Range)


Posted by Juan Pablo G. on January 24, 2002 11:57 AM

As this is written, it would copy every cell that changes to Sheet2. If you want to restrict this to a certain area use something like:

Set MyRange = Union(Range("A1:B5"),Range("C8"))
If Not Intersect(Target,MyRange) is Nothing then
Target.Copy etc.
End If

Juan Pablo G. As I understand it this mirrors the whole sheet not just a specific cell. Is there a way to modify this to mirror a single cell or even a specific range of cells? P.S. Thanks for starting me down the right trail! : Mark, you could do that in one line : Target.Copy Sheets("Sheet2").Range(Target.Address) : Juan Pablo G.


Posted by Mark O'Brien on January 24, 2002 12:07 PM

Hey, I said it was a terrible attempt. :)

You may have noticed from some of my code examples that I enjoy using many lines, where others use only one. Mark, you could do that in one line Target.Copy Sheets("Sheet2").Range(Target.Address) Juan Pablo G. : This is an absolutely terrible attempt. Perhaps someone can expand upon it. It does what you want it to do, so long as you change the cell contents and not just the formatting. Anyway, give it a try. Put this code on the worksheet that is being changed, i.e. not the duplicate page. : Private Sub Worksheet_Change(ByVal Target As Range)

Posted by Todd on January 24, 2002 12:09 PM

Perfect!, Thanks guys As this is written, it would copy every cell that changes to Sheet2. If you want to restrict this to a certain area use something like: Set MyRange = Union(Range("A1:B5"),Range("C8"))


Posted by Todd on January 25, 2002 10:07 AM

So what I wound up with was:

Private Sub Worksheet_Change(ByVal Source As Range)
Set SourceRange1 = Range("A1:A5")
Set SourceRange2 = Range("C1:C5")
Set SourceRange3 = Range("E1:E5")

If Not Intersect(Source, SourceRange1) Is Nothing Then
SourceRange1.Copy Worksheets("Sheet2").Range("A11")
ElseIf Not Intersect(Source, SourceRange2) Is Nothing Then
SourceRange2.Copy Worksheets("Sheet2").Range("C11")
ElseIf Not Intersect(Source, SourceRange3) Is Nothing Then
SourceRange3.Copy Worksheets("Sheet2").Range("E11")
End If
End Sub

It works but I can't help but think there should be a more streamlined approach. Perfect!, Thanks guys : As this is written, it would copy every cell that changes to Sheet2. If you want to restrict this to a certain area use something like