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....?

 

[Andrew Poulsom]

That's using Q2 not R3.

 

[AD_Taylor]

Just to make sure I completely understand,

You want to run the method above to copy H2:H968 into column G, keeping only the values, every 30 seconds?

So it runs, 30 seconds later it runs again, 30 seconds after the second run it runs again etc.

If so I can do this for you?

 

[cicconmr]

This code actually works better:

Private Sub Worksheet_Calculate()
    
    'Application.ScreenUpdating = False
    
    If Second(Range("Q2")) = 30 Then
            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
            
    End If
    
    'Application.ScreenUpdating = True
    
End Sub

HOWEVER, when I uncomment out Application.ScreenUpdating = False my screen just flickers non-stop as if the program is continually running?

Is that a function of having that writen in the WorkSheet as opposed to a Module?

 

[Andrew Poulsom]

That procedure will run whenever the worksheet is calculated, and that may be frequently depending on the set-up.

 

[rguy84]

If you wanted every 30 seconds wouldn't

if second(Now())= (0 OR 30) then 
 'your copy/paste
end if

do it?

 

[AD_Taylor]

Well yes it will run very frequently.

Not sure if this is right but if you are copy and pasting values into another cell, this will trigger a calculate event which in turn triggers the code again

I suggest looking into the Application.OnTime function. This will let you trigger a macro after a certain amount of time has passed and should be put in a normal module.

 

[cicconmr]

Okay so the problem is it is switching from false to true everytime my BLP links are updated in real time....I'd assume that is where the flickering is coming from?

I can't seem to prevent the screen from updating, taking me to a hiding sheet where all my data is at....is there any way to do that with the Calculate Event?

 

[cicconmr]

If you wanted every 30 seconds wouldn't

if second(Now())= (0 OR 30) then 
 'your copy/paste
end if

do it?

How do I make this so that it works with out me clicking "Run"? Do I need to put this in a Worksheet? I'm worried that it won't operate automatically but I could be wrong...this seems like the simpliest way to do this.

 

[AD_Taylor]

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 meaning once it starts you don't need to keep running it - it automatically runs itself.

Does this work?

You'll probably also need to include this code in the ThisWorkbook area of your file:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim nextTick   

nextTick = Now + TimeValue("00:00:30")
Application.OnTime nextTick, "Update30Secs", , False
    
End Sub