Detect Sheet2 change event in Sheet1 Module through VBA

[kartiki]

I have two worksheets Sheet1 and Sheet2. I need to write function in Sheet1 module which would detect Sheet2 change event. I need this function in Sheet1 module cause Sheet1 would get duplicated with new revisions and want this code to get duplicated as well with it.

 

[Michael M]

There would have to be something in Sheet 1 to trigger the code first ??

 

[kartiki]

Lets say I need to detect A1 cell change on Sheet2 and add data validation to a range A1:A10 on Sheet1, based on Sheet2-A1 cell's content.

 

[Michael M]

That's fine but something will have to change in Sheet 1 to activate the code !!!
Have you considered putting the code in a module, then calling it from Sheet2 with a worksheet change event ??

 

[kartiki]

That's fine but something will have to change in Sheet 1 to activate the code !!!
Have you considered putting the code in a module, then calling it from Sheet2 with a worksheet change event ??

Sheet1 module needs to have a function called

Sub Sheet2_change(ByVal Target As Range) ...this gets triggered on any cell change in sheet2
if target.row = 1 and target.column = 1 and target.value <> "" then
Sheets("Sheet1").range(cells(1,1),cells(1,10)).value = target.value
end if
End Sub