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

 

[bobby786]

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")

Wow , you are great mate , i will play with this around today , i think this will work for me .I will post request if needed.

Thanks for your time and effort.

Regards

 

[bobby786]

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")

I am running into one issue , Columns in CENTRAL and QC sheets are not same after K , so if i fill something in COL L to COL XX , it will get duplicated in QC sheet as well .

For E.G
in CENTRAL COL P is "Silk Printing Date" where as in QC sheet COL P represent "QC Info Icon" i dont want Central to duplicates after COL K to be specific . Is it doable ?

 

[Zot]

You can skip any column you like by adding more column letter other than K here

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strCol As String

strCol = Split(Target.Address, "$")(1)   ' This extract column letter
Select Case strCol
    Case "K"
    
    Case Else
        Sheets("QC").Range(Target.Address) = Target.Value2
End Select

End Sub

 

[Zot]

My bad... Did not read carefully. You said anything after column K. You do not want to list all column alphabets ?

Need to change to something else but not I need to go somewhere for a while.

 

[bobby786]

You can skip any column you like by adding more column letter other than K here

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strCol As String

strCol = Split(Target.Address, "$")(1)   ' This extract column letter
Select Case strCol
    Case "K"
   
    Case Else
        Sheets("QC").Range(Target.Address) = Target.Value2
End Select

End Sub

I want to skip the COL from L to AZ in QC sheet.
i tried the below code with changes but it does not work , may be you can help me modify it .

strCol = Split(Target.Address, "$")(1)   ' This extract column letter
Select Case strCol
    Case "L:AZ"
    
    Case Else
        Sheets("QC").Range(Target.Address) = Target.Value2
End Select

End Sub

 

[Zot]

It is easier to use column number I think. Skip anything from L

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Target.Column > 11 Then
    Sheets("QC").Range(Target.Address) = Target.Value2
End If

End Sub

 

[Zot]

I want to skip the COL from L to AZ in QC sheet.
i tried the below code with changes but it does not work , may be you can help me modify it .

strCol = Split(Target.Address, "$")(1)   ' This extract column letter
Select Case strCol
    Case "L:AZ"
   
    Case Else
        Sheets("QC").Range(Target.Address) = Target.Value2
End Select

End Sub

A range like that could not work since it is expecting a letter, not a range for strCol

 

[bobby786]

It is easier to use column number I think. Skip anything from L

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Target.Column > 11 Then
    Sheets("QC").Range(Target.Address) = Target.Value2
End If

End Sub

Amazing ! it is working fine. I will play around with it.

 

[Zot]

This will give exception to column L to AZ as I tried it

Private Sub Worksheet_Change(ByVal Target As Range)

Dim strCol As String

strCol = Split(Target.Address, "$")(1)
If Not strCol Like "[L-Z]" And Not strCol Like "A[A-Z]" Then
    Sheets("QC").Range(Target.Address) = Target.Value2
End If

End Sub

 

[bobby786]

This will give exception to column L to AZ as I tried it

Private Sub Worksheet_Change(ByVal Target As Range)

Dim strCol As String

strCol = Split(Target.Address, "$")(1)
If Not strCol Like "[L-Z]" And Not strCol Like "A[A-Z]" Then
    Sheets("QC").Range(Target.Address) = Target.Value2
End If

End Sub

Dear Zot ,

both of the solution is working , but i think i will go with number as its easy to remember .