Good morning, I am new to this forum and more generally to programming in Excel. It's a software that I really like but I'm still limited in my skills... Anyway, thank you for your attention and for this great forum. I'm looking to just live copy/mirror/synchronize a range of cells from different sheets.
Basically I have a huge sheets that includes several parts filled out by several of my employees.
And I need them to be able to work on their own sheets (in the same file) and that automatically reports to the main sheet (mine) which will group all the results together.
And conversely, if I change the main sheet, I would like it to affect their parts automatically.
I found results to do it but for rows, columns but never very concluent.
Excuse my english... I'm french.
Thank you very much.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Mirroring/synchronizing cells
0
Discussion
Hi Tdy and welcome to the Forum.
To answer you we need to know what the main and individual sheets look like and how they are named. Please edit your question and use the Add Files button to attach a representative Excel file with main sheet and say 2 employee sheets (but no personal data).
To answer you we need to know what the main and individual sheets look like and how they are named. Please edit your question and use the Add Files button to attach a representative Excel file with main sheet and say 2 employee sheets (but no personal data).
John_Ru (rep: 6142) Feb 3, '23 at 1:06 pm
Answers
0
Selected Answer
Hello Tdy,
Below is the solution I am offering. It is quite possible that John may provide a better one. Which ever one you like just remember to mark the answer as selected.
What you are looking to have happen can be achieved using the worksheet event Worksheet_Change. Each of the five sheets will have its own code. The worksheet event code is placed in the Worksheet Module. Press Alt+F11 to open the vba editor and then double click on a worksheet to open its code module.
Here is the code for the MAIN worksheet:
Private Sub Worksheet_Change(ByVal Target As Range)
' macro written by WillieD24 for TeachExcel
' sheet MAIN
Dim cAddress As Variant
' change value on TEAM A sheet
If Not Intersect(Target, Range("C13:S22")) Is Nothing Then 'Or Target.Cells.Count > 1 Then 'Resume Next
Application.EnableEvents = True
cAddress = Target.Address
Sheets("TEAM A").Range(cAddress).Offset(-10).Value = Target.Value
Application.EnableEvents = True
End If
' change value on TEAM B sheet
If Not Intersect(Target, Range("C26:S35")) Is Nothing Then 'Or Target.Cells.Count > 1 Then 'Resume Next
Application.EnableEvents = False
cAddress = Target.Address
Sheets("TEAM B").Range(cAddress).Offset(-23).Value = Target.Value
Application.EnableEvents = True
End If
' change value on TEAM C sheet
If Not Intersect(Target, Range("C39:S48")) Is Nothing Then 'Or Target.Cells.Count > 1 Then 'Exit Sub
Application.EnableEvents = False
cAddress = Target.Address
Sheets("TEAM C").Range(cAddress).Offset(-36).Value = Target.Value
Application.EnableEvents = True
End If
' change value on TEAM D sheet
If Not Intersect(Target, Range("C52:S61")) Is Nothing Then 'Or Target.Cells.Count > 1 Then 'Exit Sub
Application.EnableEvents = False
cAddress = Target.Address
Sheets("TEAM D").Range(cAddress).Offset(-49).Value = Target.Value
Application.EnableEvents = True
End If
End Sub
Here is the code for the TEAM A worksheet:
Private Sub Worksheet_Change(ByVal Target As Range)
' macro written by WillieD24 for TeachExcel
' sheet TEAM A
Dim cAddress As Variant
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("C3:S12")) Is Nothing Then
Application.EnableEvents = False
' change value on MAIN sheet
cAddress = Target.Address
Sheets("MAIN").Range(cAddress).Offset(10).Value = Target.Value
Application.EnableEvents = True
End If
End Sub
The code for Team B, C, and D sheets is the same except for the Offset value.
B Offset is 23; C Offset is 36; D Offset is 49.
The attached file is your original file with the macros added.
Hope this helps.
Cheers :-)
Discussion
@Willie - Well done, I like your solution and was thinking along similar lines.
@Tdy - I hope Willie's solution works for you. If so please remember to mark his Answer as Selected.
There are enhancements which could be made (e.g. you could password protect sheets or made only one sheet visible to an authorised owner/user) but you are new to programming so it would probably just confuse you. If you later find difficulties in use, please come back to the Forum and ask a new question (providing a file). Good luck!
@Tdy - I hope Willie's solution works for you. If so please remember to mark his Answer as Selected.
There are enhancements which could be made (e.g. you could password protect sheets or made only one sheet visible to an authorised owner/user) but you are new to programming so it would probably just confuse you. If you later find difficulties in use, please come back to the Forum and ask a new question (providing a file). Good luck!
John_Ru (rep: 6142) Feb 5, '23 at 3:37 am
It's so so so so great ! Thank you very much... it works perfectly.
I'm a beginner and alrrdy passionned about excel.
Would iit be possible to automatically add a row the same way (if I had one or delete one on a side, it does the same on the other side).
I'm a beginner and alrrdy passionned about excel.
Would iit be possible to automatically add a row the same way (if I had one or delete one on a side, it does the same on the other side).
Tdy (rep: 2) Feb 5, '23 at 4:13 pm
@Tdy - the first time you see VBA work, it feels like magic but it can do so much more.
Willie answered your original question well so please ask a new question on adding or deleting a row, while "mirroring" sheets. .
Willie answered your original question well so please ask a new question on adding or deleting a row, while "mirroring" sheets. .
John_Ru (rep: 6142) Feb 5, '23 at 4:38 pm
@Tdy - Thank you for selecting my answer. Adding/deleting rows would be possible using vba. When you post your new question be sure to include a file. More details are also required. Under what conditions would a new row be required? Will the new row always be added to the bottom of the table for the particular team? Or will you want a new row added to each team table? Under what conditions would you want to delete a row? These, and any other details you might think of are needed to be able to provived a solution (beyond saying "Yes, it is possible). Adding functionality to add/delete a row will also require the Worksheet_Change code to be modified.
Cheers :-)
Cheers :-)
WillieD24 (rep: 557) Feb 5, '23 at 11:45 pm
0
Here an example...
I hope it will be clear enough.
Thank you !
Discussion
Thanks Tdy. I'll try to look at that tomorrow.
For the future, please note that your point above is not an Answer (a solution to a question) and could make other contributors think you have a solution (so not reply) - that's what I asked you to "edit your question".
For the future, please note that your point above is not an Answer (a solution to a question) and could make other contributors think you have a solution (so not reply) - that's what I asked you to "edit your question".
John_Ru (rep: 6142) Feb 4, '23 at 9:10 am