Is it anything wrong with this code?

[daniboymu]

so I have a code for a macro and everytime I run, my excel freezes, Idk what to do anymore, please someone could help me

CODE:

Sub Atualizar()

    Do
        If Time < TimeValue("21:00:00") Or Time >= TimeValue("22:15:00") Then End
        Rows(7).Insert
        Range("A8:DG8").Value = Range("A5:DG5").Value
        Application.Wait (Now + TimeValue("0:00:05"))
    Loop
   
End Sub

 

[daniboymu]

I need to use the sheet while this macro is running, how could I do that?

 

[mrshl9898]

DoEvents lets you use Excel, though I don't think it'll let you use it while Wait is running.

Code works fine here, but since it's constantly running it may look frozen.

Sub Atualizar()

    Do
        If Time < TimeValue("21:00:00") Or Time >= TimeValue("22:15:00") Then End
        Rows(7).Insert
        Range("A8:DG8").Value = Range("A5:DG5").Value
        DoEvents
        Application.Wait (Now + TimeValue("0:00:05"))
    Loop
  
End Sub

 

[mrshl9898]

The Wait method suspends all Microsoft Excel activity and may prevent you from performing other operations on your computer while Wait is in effect. However, background processes such as printing and recalculation continue.

Application.Wait method (Excel)

Office VBA reference topic

docs.microsoft.com

 

[daniboymu]

DoEvents lets you use Excel, though I don't think it'll let you use it while Wait is running.

Code works fine here, but since it's constantly running it may look frozen.

Sub Atualizar()

    Do
        If Time < TimeValue("21:00:00") Or Time >= TimeValue("22:15:00") Then End
        Rows(7).Insert
        Range("A8:DG8").Value = Range("A5:DG5").Value
        DoEvents
        Application.Wait (Now + TimeValue("0:00:05"))
    Loop
 
End Sub

Could you please help me, because I need to keep use the sheet while the macro is running. there is something I can do about it?

 

[daniboymu]

because all I want is the macro running on a especific time and also running the macro I can use the sheet

 

[mrshl9898]

Try this:

Sub Atualizar()

    Do
        If Time < TimeValue("21:00:00") Or Time >= TimeValue("22:15:00") Then End
        Rows(7).Insert
        Range("A8:DG8").Value = Range("A5:DG5").Value
        WasteTime
    Loop
   
End Sub

Sub WasteTime()
Dim time1, time2

time1 = Now
time2 = Now + TimeValue("0:00:05")

    Do Until time1 >= time2
        DoEvents
        time1 = Now()
    Loop

End Sub

How to pause for specific amount of time? (Excel/VBA)

I have an Excel worksheet that has the following macro. I'd like to loop it every second but danged if I can find the function to do that. Isn't it possible? Sub Macro1() ' ' Macro1 Macro ' Do

stackoverflow.com

 

[daniboymu]

Try this:

Sub Atualizar()

    Do
        If Time < TimeValue("21:00:00") Or Time >= TimeValue("22:15:00") Then End
        Rows(7).Insert
        Range("A8:DG8").Value = Range("A5:DG5").Value
        WasteTime
    Loop
 
End Sub

Sub WasteTime()
Dim time1, time2

time1 = Now
time2 = Now + TimeValue("0:00:05")

    Do Until time1 >= time2
        DoEvents
        time1 = Now()
    Loop

End Sub

How to pause for specific amount of time? (Excel/VBA)

I have an Excel worksheet that has the following macro. I'd like to loop it every second but danged if I can find the function to do that. Isn't it possible? Sub Macro1() ' ' Macro1 Macro ' Do

stackoverflow.com

this is exactly what I want there is only one thing left, i'm very sorry to bother you too much. I need that the macro only runs on a especific spreedsheet, because i have 3 spreedsheets, one with the data, other with the calculations and the last one with the chart, I only Need the macro running on the database sheet, is it possible to do that?

 

[mrshl9898]

Not a worry

Sub Atualizar()

Dim ws As Worksheet

Set ws = Sheets("database")

    Do
       If Time < TimeValue("21:00:00") Or Time >= TimeValue("22:15:00") Then End
        ws.Rows(7).Insert
        ws.Range("A8:DG8").Value = ws.Range("A5:DG5").Value
        WasteTime
    Loop
   
End Sub

Sub WasteTime()

Dim bla As String

bla = Range("A1").Value

Sheets("Setup Page").Range("Z26:Z30").AutoFilter Field:=1, Criteria1:="*" & bla & "*"

End Sub