Problem with sheets that mirror each other

[Robert Crandal]

I'm trying to setup that mirror each other at all times. I am currently
handling "Worksheet_Change()" in my sheets, so any time one
sheet gets modified, I am trying to copy the contents of one sheet
to the other.

The shortened code looks as follows:

'
' Sheet 1
'
Private Sub Worksheet_Change(ByVal Target As Range)
changedCell = Target(1).Address(0, 0)
Sheet2.Range(changedCell).Value = Sheet1.Range(changedCell).Value
End Sub

'
' Sheet 2
'
'Private Sub Worksheet_Change(ByVal Target As Range)
changedCell = Target(1).Address(0, 0)
Sheet1.Range(changedCell).Value = Sheet2.Range(changedCell).Value
End Sub

This code generally works when a user changes one cell at a time. However,
(for example) if a user selects a big block of cells on Sheet1 and presses
the DELETE key, that block of cells will actually delete on Sheet1, but only
one (or two or so) cells will delete from Sheet2.

How can I modify the subroutines above to handle cases when a block of
data is highlighted in one sheet, then tell the second sheet to delete all
those
cells as well??

Thank you!!!

 

[Mike H]

Hi,

Sheet1
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet2").Range(Target.Address).Value = Target.Value
End Sub

sheet2
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet1").Range(Target.Address).Value = Target.Value
End Sub

Mike

 

[OssieMac]

Hi Robert,

Perhaps you would like to share the reason for mirroring the 2 sheets. There
might be a more efficient way of achieving this like copying the sheet before
close or save etc.

 

[Robert Crandal]

I have TWO different layouts, a red and blue layout, each with
slightly different dimensions. It gives the user 2 different ways
of instantly looking at the data. When the red sheet is toggled on,
I hide the blue sheet and the unhide the red sheet (or vice versa).

So, you see, the user only sees one sheet at a time, and I want them
to have similar data at all times. My mirror plan works except for
case when a block is highlighted and then deleted with the "Delete"
key, as the deleted cells dont all seem to copy over for some reason.

Got any ideas?

 

[Robert Crandal]

Isn't my code below similar to yours??? I'm just using a
different notation, am I not?? I don't think either code
will work for cases when a big area of cells is
highlighted and deleted (with "delete" key), as the
deleted cells dont seem to copy over from one sheet to the
other for some reason.

 

[joel]

When using a worksheet change if somebody copies a group of cells you
have to run the code for every values in the group like this

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

for each cell in Tartget
changedCell = cell.Address
Sheet2.Range(changedCell).Value = cell.Value
next Cell

Application.EnableEvents = True

End Sub

I'm not sure if this code will work because worksheet change is
inteneded only to modify the cells on the active sheet and doesn't alows
other cells to get changed. The Delkte key may not work because the
worksheet change event occurs after the cell is changed and if somebody
removed data from the cell you can't copy the data to another sheet.

You could use the function

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)


Which will occur before the user changes the cell data.

 

[Mike H]

Robert,

No our code is 'similar' and not the same so I suggest you enter my code,
select a large range of cells and hit the delete key and see what happens!

Mike

 

[Mike H]

it would be better to disable events

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Sheets("Sheet2").Range(Target.Address).Value = Target.Value
Application.EnableEvents = True
End Sub


same on sheet 2

Mike

 

[Mike H]

Joel,

I don't believe you do need to do that. Target.adddress & Target.value are
either a single cell or; if selected, a range of cells so a one liner works

Sheets("Sheet2").Range(Target.Address).Value = Target.Value

Mike

 

[Mike Fogleman]

The code I gave you a while back and the code given by this thread will only
work when cells are ClearContents. When you delete cells from sheet1, you
are shifting the remaining cells to a new position. The code uses the range
that is actually selected on sheet1 for example A1:A100, and makes sheet2
range A1:A100 the same values. If you selected Shift Cells Up, then on
sheet1, cell A101 is now in A1 and so on down the column. But in sheet2, all
that happened is that range A1:A100 equals what is now in that range on
sheet1, the rest of the cells in the column did not Shift Up, so sheet2 A101
is the same as it was, and so on down from there. I am not sure you are
going to satisfactorily get what you want from this approach. Since the
other sheet is hidden, perhaps using another event to make one sheet mirror
the other such as the Activate/Deactivate event.

Mike F

 

[john]

As an idea would the followng approach do what you want??

'sheet 1
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range
Dim rng2 As Range

Application.EnableEvents = False

Sheets(2).Cells.ClearContents

Set rng1 = Sheets(1).UsedRange

Set rng2 = Sheets(2).Range("A1")

rng1.Copy rng2

Application.EnableEvents = True

End Sub

'sheet 2
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range
Dim rng2 As Range

Application.EnableEvents = False

Sheets(1).Cells.ClearContents

Set rng1 = Sheets(2).UsedRange

Set rng2 = Sheets(1).Range("A1")

rng1.Copy rng2

Application.EnableEvents = True

End Sub