Stamp date in Sheet 1 upon Change of value in another sheets

SharanKoirala

New Member
Joined
Feb 7, 2012
Messages
18
Hi,
I have got a excel file with different sheets with the detail of different projects. The first sheet has got the list of the projects in one column and the next column is to be stamped with the latest updated date of that project in another sheets using VBA Code. I am trying to make this problem simpler as following.

Sheet 1 - Column A - contains list of projects like Project 1, Project 2, Project3 in different rows etc
Sheet 1 - Column B - I want to stamp the latest update date of the corresponding project.

and the sheet name are in the following way

Sheet 2 - Project 1, Sheet 3 - Project 2, Sheet 4 - Project 3 etc.

Now my problem is if I update the datas in in sheets Project 1, Project 2 or Project 3, I want to stamp the updated date of that project in Sheet 1 next to that project Date.

I tried the following code and saved in "ThisWorkbook" but did not work.

Private Sub Workbook_SheetChange(ByVal Target As Range)

Dim SheetName As String
Dim Found As Range
Dim Lookin As Range

If IsEmpty(Target) Then Exit Sub
SheetName = ActiveSheet.Name

Set Lookin = Worksheets("Sheet1").Range("A1:A25")

Set Found = Lookin.Find(SheetName)

If Found Is Nothing Then Exit Sub

Found.Offset(0, 1).Value = Now

End Sub

If somebody can help me in thsi ASAP I will really appreciate it.
Thanks in advance.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

SharanKoirala

New Member
Joined
Feb 7, 2012
Messages
18
Hi All,
Do you guys need some more explanation to understand my problem? Is there anybody who can help to solve this please?
 

nise23

Board Regular
Joined
Jan 6, 2012
Messages
65
Try this and let me know if it works :)

Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim SheetName As String
Dim Found As Range
Dim Lookin As Range
    If Sh.Name = "Sheet1" Then
    Exit Sub
    End If
SheetName = ActiveSheet.Name
Set Lookin = Worksheets("Sheet1").Range("A1:A25")
Set Found = Lookin.Find(SheetName)
If Found Is Nothing Then Exit Sub
Found.Offset(0, 1).Value = Now
End Sub

-nise23
 

SharanKoirala

New Member
Joined
Feb 7, 2012
Messages
18
Hi Nise, Thanks for your reply. I copied this code under Microsoft Excel Objects to ThisWorkbook. But it does not show any effect. I could not figure out what is missing there.
 

nise23

Board Regular
Joined
Jan 6, 2012
Messages
65
Works perfectly for me, puts a date & time stamp into column B of Sheet1, given that the proper sheet names are entered into column A. Do all your sheet names match?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,989
Members
416,953
Latest member
broexc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top