Solved two worksheets to synchronize for changes in either sheet

April 28, 2017 at 15:14:32
Specs: Windows 7
how can i have two sheets sync with eachother. For instance if i change the value of cell A1 on Sheet1 i would like A1 on sheet two to change to the same value. i would then like that to happen vice versa, if i change something to Sheet2 the same change would happen in Sheet 1.

See More: two worksheets to synchronize for changes in either sheet

Report •

#1
April 28, 2017 at 17:42:55
The simplest way is while you are in Sheet 1
simply press CTRL and the Sheet 2 Tab,

Now anything you do on sheet 1 is reflected on sheet 2
and anything you do on sheet 2 is reflected on sheet 1

MIKE

http://www.skeptic.com/


Report •

#2
April 28, 2017 at 18:12:59
Very true grouping sheets is the easiest way, however i have macros that wont run when the sheets are grouped. basically what i am looking for is to have two worksheets that are essentially one worksheet. The reason i want this is so that if i can apply different filters to the sheets. on one sheet i want everything to be filtered to show only tasks that are "In Progress" and "On hold" and on the other sheet i want everything to be filtered on "Completes" only. so if someone accidentally changes the task from in progress to complete they can simply go to the completed sheet and change it back. i know i could simply have this on one worksheet and the user can simply remove the filter to change the complete back to in progress. but i want to see if i can simplify it so they dont have to remove the filter and reapply again.

Report •

#3
April 28, 2017 at 20:39:46
✔ Best Answer
Put this in the Sheet module for Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Cells.Count = 1 Then
  Sheets(2).Range(Target.Address) = Target
 End If
End Sub

I assume you can figure out what goes in the Sheet module for Sheet2.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
April 29, 2017 at 06:16:18
Should probably turn off events during the update, DerbyDad03

How To Ask Questions The Smart Way


Report •

#5
April 29, 2017 at 09:51:02
Have you given any thought to using Conditional Formatting to highlight your three conditions?

Something like:

Green = Completed
Yellow = In Progress
Red = On Hold

You can then tell, at a glance, what is happening to to which job.

MIKE

http://www.skeptic.com/


Report •

#6
May 2, 2017 at 17:19:31
DerbyDad, just so i am sure i just change Sheets(2) to Sheets(1) when posting the code in Sheets (2).

Report •

#7
May 2, 2017 at 17:49:36
i added this code on worksheet 2 and changed the 2 to a 1. but i keep getting an error Method "Range object_worksheet failed...

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Cells.Count = 1 Then
  Sheets(1).Range(Target.Address) = Target
 End If
End Sub



Report •

#8
May 2, 2017 at 18:20:54
I figured it out that i had to turn off events during the update, Thanks Razor for the heads up.

Report •

Ask Question