Hi,
With refer to https://www.youtube.com/watch?v=gIPdx4-rqGg&t=902s
I want to write this below code in module.
But I see error of "424 Object required" when copied the code and run in Module.
Hence, Can you please help me with the correct coding or any changes required for the same
Private Sub Worksheet_Change(ByVal Target As Range) Dim myTableRange As Range Dim myDateTImeRange As Range Dim myUpdatedRange As Range Set myTableRange = Range ("A2:D10") If Intersect(Target, myTableRange) Is Nothing Then Exit Sub Set myDateTimeRange = Range("E" & Target.Row) Set myUpdatedRange = Range ("F" & Target.Row) If myDateTimeRange.Value = "" Then myDateTimeRange.Value = Now End If myUpdatedRange.Value = Now End Sub
Regards,
Nitil
Hi John,
Thanks for your quick response.
I wanted my workbook to give results like attached sheet," Run 2 Macros at Set Intervals v0 c".
Where, if we run the macro in sheet 1 and than after sometime if we change the sheet to sheet 2, than also the macro is running in sheet1 and giving result like,
if when i start the macro in sheet 1, it start giving results like 1, 2, 3 and when i come to sheet 2 than after one minute or so, if i go to sheet 1, than i see the macro is continuously run in background and see the results like 8, 9,10, etc. means the macro had not stopped when i change the sheet1 to sheet2.
Further, I am finding the problem in my sheet, that if i run the macro in sheet1 than when i go to sheet2, than the macro stop running means i have to keep sheet 1 active for the result.
I have attached my sample excel sheet where i require the same result like in attached sheet, "Run 2 Macros at Set Intervals v0 c" where if I run the macro in sheet1, and if i change the sheet from sheet 1 to sheet 2, the macro should run continuously in the sheet .
I want below code to run in module for the same.
Sub Worksheet_Change()
Dim myTableRange As Range
Dim myDateTimeRange As Range
Dim myUpdatedRange As Range
Sheet23.Range("B2:AP200") = Sheet23.Range("B2:AP200")
Set myTableRange = Range("AH2:AN300")
"If Intersect(Target, myTableRange) Is Nothing Then Exit Sub"
Set myDateTimeRange = Range("AO" & Target.Row)
Set myUpdatedRange = Range("AP" & Target.Row)
If myDateTimeRange.Value = "" Then
myDateTimeRange.Value = Now
End If
myUpdatedRange.Value = Now
Call macro_timer
End Sub
Request you to Please help me with the solution.
Thank you & Best Regards,
Nitil
Revision 1
Hi,
Please find two attachments:
1. Run 2 Macros at Set Intervals v0 c
2. My sample Test excel sheet
In "Run 2 Macros at Set Intervals v0 c", Whenever I run Macros in the sheet1, it working fine. Also if whenever i shift the sheet from sheet 1 to sheet 2, than also the macros is continuously running and updating the data.
In "My sample Test excel sheet", I am facing issue: Whenever I run the macros, its working, but i have to keep the same sheet active, but whenever i shift the sheet from sheet 1 to sheet 2, than the macros stops running in the required sheet and start running in sheet 2.
I wanted the macros to give me continuous results in sheet1 inspite of shift from sheet 1 to sheet 2.
I wanted result same result lik "Run 2 Macros at Set Intervals v0 c" in My sample Test excel sheet.
Further, My requirement is that the sheet1 is formula based sheet which is linked to sheet 2 which have data base. So, whenever there is updation in sheet 2, than sheet 1 is automatically updated, but i want to get notified whenever there is any change in sheet 1.
Secondly, due the formula based sheet1, there should be any change to the sheet to get notified.
Hence, i have taken the timestamp and timer to get updated.
Hope you understand my requirement and would request you to please help me for the solution.
Thanks,
Nitil
Revision 2
Hi,
Please find my latest file i.e. "My sample Test excel sheet" which help you understand what i am looking for.
> Sheet 1 is updated through formula from sheet 2
I think there is no vba code for tracking formula based sheet. Hence, I have wrote VBA of macro for column D which update every interval i.e 1 & 2 and timestamp to capture the changes in sheet 1.
I wanted to be notified when there is any change in sheet 1. Hence, i have created pivot table for the same.
Here, my worry is when the macros is running and whenever i shift to any other sheet, the macros overwrite in other sheet which i have shifted to.
I wanted the macros to run to specific sheet rather any sheet which is shifted to view.
Please help me to limit the macros to run to specific sheet i.e. (sheet 1) and not any other sheet.
Hope you understand my requirement.
Thanks,
Nitil