Worksheet_Change Question

[cicconmr]

 Private Sub WorkSheet_Change(ByVal Target As Range)
    If Target.Address = "$Q$2" Then
        If Second(Target) = 30 Then
        
            Worksheets("Hardware").Select
            Range("H2:H2968").Select
            Selection.Copy
            Range("G2").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False

    
        End If
    End If
End Sub

What I want this to do is preform the copy/paste replacement everytime the Cell R3 changes to 30 seconds. In simplier terms, I want to perform an operation every 30 seconds.

This seems like it should work but doesn't....?

 

[cicconmr]

Try this:

Sub Update30Secs()
 
    Dim nextTick
 
        Worksheets("Hardware").Select
        Range("H2:H2968").Select
        Selection.Copy
        Range("G2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("A1").Activate
 
    nextTick = Now + TimeValue("00:00:30")
    Application.OnTime nextTick, "Update30Secs"
 
 
End Sub

Place this code inside a normal module. Click Run and run the macro once. When the macro gets to the line Application.OnTime it sets an alert to re-run itself in 30 seconds time. After 30 seconds have passed it runs itself again.

Does this work?

This works but I have to run to intiate it, can I place this into a WorkBook_Open to get it going when the book is open?

 

[rguy84]

Open up "this workbook" and add Update30Secs to the Workbook_open event.

 

[AD_Taylor]

Yep just like this:

Private Sub Workbook_Open()
    
Call Update30Secs
    
End Sub

 

[cicconmr]

Any idea where I should be putting my

Application.ScreenUpdating...

Because I have it in the Sub "Update30Secs"

And it does not work, everytime that program runs the screen goes to the hidden screen.

 

[AD_Taylor]

Any idea where I should be putting my

Application.ScreenUpdating...

Because I have it in the Sub "Update30Secs"

And it does not work, everytime that program runs the screen goes to the hidden screen.

What do you mean by it goes to the 'hidden screen'?

ScreenUpdating won't move this. Personally I wouldn't include any ScreenUpdating bits as its not needed here.

Am I right thinking you want the process to run without changing the currently selected cell?

 

[cicconmr]

The issue was, If you select a screen in your code as I was, it does not matter if you use Application.ScreenUpdating = False it will still switch sheets.

You see I have an interface panel for the user, and all the data that is being acted upon in a hidden sheet....so I just simply compressed the code so it doesn't select anything, just directs where to copy and paste.

With A.SU = False on and this corrected I can stay on the interface so the User doesn't have to see what is going on behind the scenes.

Thank you guys so much for this....this is a great tool to have. Now I'm going to try and tackle setting up some alerts.

Matt