I have 4 macros which inserts a row in workbook (at specific interval of time). These macros run in a endless loop. The problem arises if any other workbook is open and active at the same time. The macro tries to update this active workbook.
Can I ensure that macro runs for only specific workbook (whether the workbook is active or not)
Here is the code:
Code
Option Explicit
Public RunWhen As Double
Public RunWhat As String
Dim wCount
Sub Macro3()
'Initialize counter
wCount = 100
Call Macro4
End Sub
Sub Macro4()
If wCount > 0 Then
' If counter greater than zero then update excel with counter and
' call macro to decrease the counter
Cells(1, 2).Value = wCount
RunWhat = "Macro5"
RunWhen = Now + TimeValue("00.00.10")
Application.OnTime RunWhen, RunWhat
Else
' If counter equal to zero than call macro to insert row
Cells(1, 2).Value = wCount
Call Macro6
End If
End Sub
Sub Macro5()
'Macro to decrease the counter
wCount = wCount - 10
Call Macro4
End Sub
Sub Macro6()
'Macro to insert row
Rows("4:4").Select
Selection.Insert Shift:=xlDown
Rows("3:3").Select
Selection.Copy
Rows("4:4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' Call the initial Macro to restart the loop
Call Macro3
End Sub
Display More
Error is coming in Selection.PasteSpecial command.