Sync Data Between Sheets

[bobby786]

I want the Columns from different sheet in the attached worksheet to be synced reciprocally. E.g. Sheet1 (CENTRAL) cell C8 is "producer", there is a similar cell in Sheet4(QC) C8 "producer", I want them all to be updated as soon as i enter data in Sheet1(CENTRAL). So that when I enter something in C8 of Sheet1(CENTRAL), it also appears in cells C8 (QC) Likewise, if i enter something in cell C9 (CENTRAL) I want the content to appear in Sheet4((QC) C9 the other linked cells and so on.

The next step would be to extend this code to work with other columns in the sheet , too. E.g. "Invoice" , "QTY" "Product Name" across all sheets and so on

Thanks a lot in advance to anyone who tries to help me!

The file is located here:

BOOK_2020.V3_2020-10-03.xlsm

drive.google.com

 

[Zot]

Say you are on Sheet1.
Press Shift and then click last sheet in workbook will select all sheets.

Then if you type anything is range A1, it will be duplicated in all selected sheet. No need code unless you prefer code

 

[bobby786]

Say you are on Sheet1.
Press Shift and then click last sheet in workbook will select all sheets.

Then if you type anything is range A1, it will be duplicated in all selected sheet. No need code unless you prefer code

That is very nice trick , and it very useful , but my case is different , its hard for user to remember select the sheets every time for each entry with specific keys , i would prefer a automatic way , i don't mind Code , there is plenty of it already on my worksheet i think LOL , ( i have very very limited knowledge of coding , all credits to this forum users who has helped me), because there is not only one columns which need to be synced there is multiples columns , and each columns will have alot of entries in a cell.

 

[Zot]

I was about to leave home when you replied.

You can try this. This will sync all sheets if any change in any sheet in workbook. Put the code in ThisWorkbook class module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim ws As Worksheet

Application.EnableEvents = False
For Each ws In Sheets
    If Not ws.Name = Sh.Name Then
        ws.Range(Target.Address) = Target.Value2
    End If
Next
Application.EnableEvents = True

End Sub

 

[bobby786]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim ws As Worksheet Application.EnableEvents = False For Each ws In Sheets If Not ws.Name = Sh.Name Then ws.Range(Target.Address) = Target.Value2 End If Next Application.EnableEvents = True End Sub

First thing in the morning to see your reply makes me very happy. I have tried to insert the code in Module1 , but its not working , i mean i try to enter the data in Central sheet but it does not appear in QC sheet , dont know what i am doing wrong.

i have attached the copy of sheet again here.

BOOK_2020.V3_2021-04-06 - Copy.xlsm

drive.google.com

 

[Zot]

Not in Module but in ThisWorkbook.

In VB Editor you can see Sheet1, Sheet2, etc and ThisWorkbook. Double-Click ThisWorkbook and paste the code there

 

[Zot]

Note that this is event triggered macro. It can be in worksheet or workbook level. I made it a workbook event as it detect any change in any sheet in workbook.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

This line is defaulted depending on which event you select.

 

[bobby786]

Not in Module but in ThisWorkbook.

In VB Editor you can see Sheet1, Sheet2, etc and ThisWorkbook. Double-Click ThisWorkbook and paste the code there

Its working now , but it does not serve the purpose i was looking for , or may be i did not explained it well in the first place. Let me try again.
My workbook has 4 sheets
Sheet1 (CENTRAL)
Sheet2 ( QC)
Sheet3 ( MATERIAL)
Sheet4 ( LIST)

Where Sheet1 COL A:K is common in Sheet2 , with your code if i enter the data after K Col in sheet1 it also appears in Sheet2 as well which is wrong , because Sheet2 after COL K its different information , similarly i dont want that to sync with all 4 sheet , just two sheets , Sheet1 and Sheet2 , is it possible to do it ?

 

[Zot]

Can just simply put under Sheet1 (CENTRAL) this line

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("QC").Range(Target.Address) = Target.Value2
End Sub

Any change in CENTRAL will be duplicated in Sheet("QC")

 

[Zot]

If you have several sheets to sync then can make put this under Sheet1 (CENTRAL)

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet

For Each ws In Sheets
    Select Case ws.Name
        Case "QC", "sheetname1", "sheetname2", "sheetname3"
            ws.Range(Target.Address) = Target.Value2
    End Select
Next

End Sub